你遇到過哪些MySQL的坑？

12-30

大家公認的坑，或者遇到過的特別的坑.
比如，系統本身設計上的問題.
或實現上的問題. 或使用方式的問題.
比如要慎用enum之類.

總之，作為一個MySQL的小白, 希望儘早避開一些可能遇到的坑.
也希望能通過一個問題來搜集人民群眾遇到的各種奇怪的坑.

MySQL中有哪些坑？

這個不知道如何回答好，但是我想說的是，MySQL支持不夠好的，而不是過去存在現在不存在的問題：

1.MySQL 因只支持嵌套循環演算法，導致設計資料庫表結構的時候要非常謹慎，規避JOIN連接演算法的支持問題；

2.資料庫表的在線DDL，雖然MySQL5.6開始支持，但是很多情況下還是不能完全支持；

3.MyISAM引擎損壞的時候，若是使用REPAIR或myisamchk則可能出現刪除表數據的風險；

4.MyISAM引擎表多大量的INSERT和DELETE的情況下，容易產生碎片，從而導致表損壞，故需要定期執行OPTIMIZE LOCAL TABLE ；

...............................................................

最近正好總結了一些，從研發角度來看，小問題還是不少，實際使用中還更多，漏掉了一些，知乎的編輯器不太好用，原文可以看這裡 MySQL中的那些坑

並沒有調侃MySQL的意思，我對開源非常認同和尊重，本文只是闡述一些問題

IN子句邏輯問題

這個是在給同事調BUG時發現的,展示之前先初始化一些數據.

create table mysql_pitfalls( c1 int, c2 varchar(128), c3 datetime, c4 timestamp ); -- 插入測試數據 insert into mysql_pitfalls(c1,c2,c3,c4) values(1,"1",now(),now()); insert into mysql_pitfalls(c1,c2,c3,c4) values(2,"2",now(),now()); insert into mysql_pitfalls(c1,c2,c3,c4) values(3,"3",now(),now()); insert into mysql_pitfalls(c1,c2,c3,c4) values(4,"4",now(),now());

下面我們分別執行以下兩條SQL

mysql&> select * from mysql_pitfalls where c1 in (1,2,3); +------+------+---------------------+---------------------+ | c1 | c2 | c3 | c4 | +------+------+---------------------+---------------------+ | 1 | 1 | 2015-06-06 19:00:05 | 2015-06-06 19:00:05 | | 2 | 2 | 2015-06-06 19:00:08 | 2015-06-06 19:00:08 | | 3 | 3 | 2015-06-06 19:00:11 | 2015-06-06 19:00:11 | +------+------+---------------------+---------------------+ 3 rows in set (0.00 sec)

這條SQL很簡單,C1列是數值型的,IN邏輯正確．接下面再看一句有邏輯問題的查詢，去IN一個字元串

-- 瞬間就被玩壞了 mysql&> select * from mysql_pitfalls where c1 in ("1,2,3"); +------+------+---------------------+---------------------+ | c1 | c2 | c3 | c4 | +------+------+---------------------+---------------------+ | 1 | 1 | 2015-06-06 19:00:05 | 2015-06-06 19:00:05 | +------+------+---------------------+---------------------+ 1 row in set, 1 warning (0.00 sec)

同樣是數值型C1列,查詢如果IN的條件是一個帶逗號的字元串,IN條件會錯誤命中字元串中第一個逗號之前的數字.雖然這條SQL寫錯了,但這本身算是一個邏輯錯誤:明明不相等,IN去處怎麼能匹配成功呢.再者,由於錯誤返回了每一條數據,有時候會麻痹開發和測試，誤認為功能沒有問題.

PS: 可以試試執行IN ("1,2,3","2,3,4"), 會發現MySQL會求每一個帶逗號字元串的第一個值.

時間精度丟失(5.6解決)

MySQL在5.6之前，無論是DATETIME類型或是TIMESTAMP類型都無無法存儲毫秒&，以至於在對時間有毫秒精度要求的場景下,我直接選用了INT型作為存儲時間的類型.

首先展示一下，MySQL提供的時間函數，是能夠支持毫秒的

mysql&> select microsecond("2015-1-1 12:00:00.3213") microsecond; +-------------+ | microsecond | +-------------+ | 321300 | +-------------+ 1 row in set (0.00 sec)

之前在測試表中，分別創建了DATETIME和TIMESTAMP的兩個欄位,C1和C2，我們使用microsecond函數測試一下

-- 使用同樣的日期數據，插入到C3,C4中 mysql&> insert into mysql_pitfalls(c1,c2,c3,c4) values(5,"5","2015-1-1 12:00:00.3213","2015-1-1 12:00:00.3213"); Query OK, 1 row affected (0.05 sec)

-- 執行查詢，測試microsecond，全部精度丟失了 mysql&> select c1,c2,microsecond(c3),microsecond(c4) from mysql_pitfalls; +------+------+-----------------+-----------------+ | c1 | c2 | microsecond(c3) | microsecond(c4) | +------+------+-----------------+-----------------+ | 1 | 1 | 0 | 0 | | 2 | 2 | 0 | 0 | | 3 | 3 | 0 | 0 | | 4 | 4 | 0 | 0 | | 5 | 5 | 0 | 0 | +------+------+-----------------+-----------------+ 5 rows in set (0.00 sec)

無論是DATETIME和TIMESTAMP，不管是NOW()函數還是帶毫秒的時間字面量，MySQL的最小存儲精度都是秒，毫秒精度都丟失了．

官方文檔對這個問題分別有描述，MySQL :: MySQL 5.1 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types，有這麼一段

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. Although this fractional part is recognized, it is discarded from values stored into DATETIME or TIMESTAMP columns. For information about fractional seconds support in MySQL, see http://dev.mysql.com/doc/refman/5.1/en/fractional-seconds.html.

而在5.6.4的文檔，已經明確表示這個問題已經修復了．

IFNULL與日期類型問題

MySQL普通的日期操作還算方便，Date，String與Double類型之都有隱式轉換．但轉換的程度有點過了，導致在操作日期時總感覺像是寫弱類型的腳本編程讓人不放心，而實事上這三者之前的隱式的轉換確實會引起很多的問題都需要注意的．不過這裡不說轉換的問題，說一個IFNULL的返回類型小問題．

接著之前的表和數據，插入一條明顯大於當前日期的記錄

mysql&> insert into mysql_pitfalls(c1,c2,c3,c4) values(7,"7",date_add(now(),interval 10 day),date_add(now(),interval 10 day)); Query OK, 1 row affected (0.07 sec)

mysql&> select c1,c3 from mysql_pitfalls; +------+---------------------+ | c1 | c3 | +------+---------------------+ | 1 | 2015-06-06 19:00:05 | | 2 | 2015-06-06 19:00:08 | | 3 | 2015-06-06 19:00:11 | | 4 | 2015-06-06 20:08:05 | | 5 | 2015-01-01 12:00:00 | | 7 | 2015-06-30 16:15:50 | +------+---------------------+ 7 rows in set (0.00 sec)

再分別執行下面兩條測試腳本

＃　這一句邏輯和結果都正確 mysql&> select c1,c3 from mysql_pitfalls where c3 &>= now() + 5; +------+---------------------+ | c1 | c3 | +------+---------------------+ | 7 | 2015-06-30 16:15:50 | +------+---------------------+ 1 row in set (0.00 sec)

#　下一句的ifnull邏輯上沒有作用，邏輯與第一條一致，但結果集卻不一致:查不出任何數據 mysql&> select c1,c3 from mysql_pitfalls where ifnull(c3,c3) &>= now() + 5; Empty set (0.00 sec)

兩條SQL的邏輯是一致的，但結果卻不一致，網上說是因為IFNULL函數返回了字元型，類型發生了變化，通過查看官方文檔，確實有說明IFNULL後類型會發生變化．內部過程是怎麼樣的可以拋一邊，我也確實沒有查到，邏輯一上應該一致的兩次測試出來的分歧，這才是重要的問題

不過我們可以試試，可以繼續測試一下到底是什麼類型

mysql&> create table tmp as select now() + 5 as dateplus, c3, ifnull(c3,c3) trans_c3 from mysql_pitfalls; Query OK, 7 rows affected (0.19 sec) Records: 7 Duplicates: 0 Warnings: 0

不確認在新建表的過程中是否發生了新的隱式轉換，從結果上來看，IFNULL返回了DATE類型，與原始類型一致．

總之在處理日期，特別是在各種類型比較，使用多種操作符，函數做操作時要很小心驗證結果．如開頭所說，可以將MySQL中的日期類型處理視為弱類型語言編程，需要多留意．而且MySQL這種類型的問題還不少．

更新時，表無法做為條件嵌套引用

我們嘗試從表中刪除一行時間最大的列，其實可選的方法不少．但如果採用下面的語句，會拋出異常

mysql&> delete from mysql_pitfalls where c4 = ( select max(c4) d from mysql_pitfalls); ERROR 1093 (HY000): You can"t specify target table "mysql_pitfalls" for update in FROM clause

如果只是想刪除最大一行的話，繞開的方法不少，也不算上讓人鬱悶．但最讓人鬱悶的是，其它這條SQL稍微改造，再嵌套一層使子查詢徹底變成內存表就可以使用了，而MySQL自身並沒有做這樣的優化

--　再嵌套一層就可以正常使用了 mysql&> delete from mysql_pitfalls where c4 = (select max(d) from ( select max(c4) d from mysql_pitfalls) a ); Query OK, 1 row affected (0.04 sec)

當然，這隻能算＂體驗＂問題．SQL上的易用性問題，就先舉這麼一個例子

字元串前後空格問題

如果varchar列的值前後存在空格，那將引起一起邏輯問題，以5.5為例，先看對查詢條件的影響:

--插入一條帶前後空格的字元串 mysql&> insert into mysql_pitfalls(c1,c2,c3,c4) values(41,"4 ",now(),now()); Query OK, 1 row affected (0.03 sec)

mysql&> select * from mysql_pitfalls where c2 = "4"; +------+------+---------------------+---------------------+ | c1 | c2 | c3 | c4 | +------+------+---------------------+---------------------+ | 41 | 4_ | 2015-06-06 20:07:22 | 2015-06-06 20:07:22 | --錯誤命中 | 4 | 4 | 2015-06-06 20:08:05 | 2015-06-06 20:08:05 | +------+------+---------------------+---------------------+ 2 rows in set (0.00 sec)

MySQL 5.0.3之後，VARCHAR的存儲時，前後空格能夠正確存儲，但在作為查詢條件時，前後空格將會被忽略，引起了查詢和插入時的邏輯不一致．而定長的CHAR邏輯又不相同，還與MySQL模式相關，MySQL :: MySQL 5.5 Reference Manual :: 11.4.1 The CHAR and VARCHAR Types具體的說可以看看這裡，講的很清楚．

類似於這樣的問題，無論怎麼處理只要邏輯一致，我們都可以視為MySQL的特性或規則（FeatureRule）．但問題就在於，這些個＂特性＂的表現並不完全符合規則，不同場景的下表現缺乏一致性，由此造成了一些誤解．

下面就有一個例子，證明這些規則缺乏相互推導，接著剛才，如果這時我們嘗試在建一個唯一索引，會拋出異常

mysql&> ALTER TABLE mysql_pitfalls ADD UNIQUE INDEX `c2_UNIQUE` (`c2` ASC);

ERROR 1062 (23000): Duplicate entry "4 " for key "c2_UNIQUE"

在存儲時被保留的空格，在創建索引時又被忽略了: 4"和"4 "被索引認為是相同的值．

忽略或者不忽略，多種場景下，並不具備普遍的適用性．

Group By: 選取非分組列

這個可能是我遇到第一個讓人奇怪的功能，算起來，這可能不算做＂坑＂，而更像是MySQL的特色．

在其它關係型資料庫中，在擁Group By子句的情況下，只能select出被分組的列，對於非分組的列，除非使用聚合函數，這列將無法選擇，原理也很簡單，畫一個二維表就能明白．但是在MySQL中，即使在Select中選擇了非分組，MySQL也不會拋出語法錯誤，而是會默認選擇這一組中這列的最後一個值．

從一方面來講，這個功能提供了很多的靈活性．但另一方面，由於這個過程是靜默，不會提示．會對開發和測試造成一定程度的麻痹（這種SQL不符合嚴謹的邏輯），而且造成了程序的不確定性．這個功能可以通過顯式修改MySQL運行模式，變得和普通RDBMS一致: MySQL :: MySQL 5.5 Reference Manual :: 5.1.7 Server SQL Modes

JDBC與TINYINT(1)

無傷大雅的小毛病，更多的應該算是JDBC驅動的問題．

如果資料庫中聲明欄位類型是TINYINT(1)，那通過JDBC拿到的值，默認將會是Boolean型，其中，如果等於０，則為false，否則為true，注意的是，即使小於0，如-1，值也為True．

可以通過resultSet.getInt()方法獲取到正確的值，如果使用框架也可以顯式指定類型，但如果想採用相對弱類型的編程方式，如使用map或者json之類的，這那將會變得有點煩，無論是框架配置或者是代碼，總得去轉換一下．

除此之外，也可以在連接字元串上加下tinyInt1isBit=false參數，這樣從TinyInt(1)拿到值將會是數值型．另外有趣的是，如果將欄位類型聲明為TinyInt(2)，只要其Length大於1，那這個值又將默認是數值型了．

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

即使站在研發角度，而非DBA角度，MySQL在使用上的小毛病還不少，有一些記不太清了，只記得曾經踩過的雷應該不少．但有一點好，現在MySQL官方開發文檔很完整，查閱很方便．

死鎖

太多了回頭寫個專欄吧。

比如

int(11) 和 int(10) 里的數字到底什麼意思？

varchar(10) 的10是位元組還是字元？

timestamp和datetime到底什麼區別？

NULL到底是什麼概念？

什麼是隱式轉換？

整型越界會導致什麼？

如果有人回復，我再填上答案。

今天剛遇到，發上來

select * from users where app_id=16519794949 and openid="sadfasdf" limit 1

select * from users where app_id="16519794949" and openid="sadfasdf" limit 1

第二個走索引，第一個不走索引，就是因為app_id是varchar類型

當然這不能關不怪mysql，當mysql遇到php這個語言就悲劇了，php裡面採用pdo綁定生成的sql語句裡面會自動判斷你綁定的數據類型生成有引號還是沒有引號

可是php一個不小心app_id就被隱式的轉換為整型了，如

$arr = ["app_id"=&>"16519794949"]; $arr2[$arr["app_id"]] = [.....]

然後app_id就坑爹了

今天剛剛從坑裡爬出來，怒答！

起因是這樣的，老師結課布置了課程設計，指定要用MySQL來完成，所以最近有妹紙來讓我幫忙debug她的課設。

所以你懂的，答主必然是興沖沖全力以赴的準備完成妹紙的這個要求啊！

嗯，這個就是事件的背景了。下面，開始正文

┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄華麗的分割線┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄

因為妹紙的要求，答主快速的把要測試的代碼編寫完成了，然後在宿舍隨便(我才不會告訴你這個隨便才是最大的bossT^T)搜了個mysql，下載好就去圖書館準備放手大幹一場，之後就可以在把順利調試完成的代碼交給妹紙，收穫妹紙驚嘆的眼神與大段的感謝了！！！嗯，理想很美好！

到了圖書館，答主想：嗯，debug應該是很簡單的，就不用插電源了，所以就直接開始吧。然後答主就動工了，coding過程無比順利，沒什麼錯誤，然後就是安一下資料庫(mysql）做個測試就好了。答主把之前下好的資料庫解壓好，發現是5.7版，有大概200多m。

這時，答主心理感覺到有一絲不對勁，因為之前用過的老版本的才只有30多m，但是答主當時也沒在意，想著新版本嘛，說不定sun公司良心發現，多加了一些有意思的插件進去而已(我才不會告訴你們，我是因為圖書館裡沒網才將就著用的→_→)。

然後答主今天的悲劇就開始了……

第一次安裝：

嗯，一步一步都很順利…咦，這裡好像有一個插件沒有下好，需要聯網……旁邊有隔壁班的女生，問她借個賬號吧……嗯，終於下好了，把賬號下線，繼續安裝ing……終於到最後一步了，咦，怎麼卡了，等一會吧。(十分鐘後)算了，還是刪掉重裝吧。

第二次安裝：

安之前查一下教程吧，興許剛剛的問題其他人也碰到過呢。答主一查，發現網上說第一次裝掉卸載不徹底，重裝就會出現剛才的情況，(可問題是我就是第一次裝啊，不管了，繼續裝吧)於是，答主按照網上的教程清理了註冊表，刪掉了文件夾，繼續安裝。結果……又卡在最後一步了……

第三遍安裝：

一定是哪裡沒做到位，全盤搜索一下，嗯，有個需要系統許可權的文件夾，打開清理清理。重複上一次的清理，重裝……

第四次安裝：

重複以上步驟時電腦沒電了，辛苦找電源，查教程，重裝ing……又找到一點殘留文件，刪掉試試……媽的，怎麼又卡了……

第五次……

第六次……

第……

……

…

就這樣，答主從下午三點，安裝一個軟體(mysql)，一直安裝到了晚上圖書館閉館，甚至回到宿舍繼續安裝，一直安到十一點，直到妹紙問我，做好了沒。結果答主只能告訴她說我連mysql都沒安好，然後被妹紙安慰了一頓，說她當時也是安錯了好久，都一樣，讓我不要灰心。我灰心？！好吧，我是灰心……

其實我已經不止是灰心，而是絕望了，竟然反過來被妹紙安慰了！！這……算了，還是等明天在做吧，我這樣想著。

躺到床上，答主突然想到一個可能，我問妹紙，她用的mysql是多大的，妹子說多30多m。

30多兆……答主下床，開機，下載，安裝，成功！

整個過程不到十分鐘……你能想像到答主當時悲憤的心情嗎？！π_π

┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄結束分割線┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄

事後，樓主到一個技術群里說了樓主的遭遇，引來一陣笑聲後才發現，原來5,7版是測試版，官方不建議下載……π_π

測試版啊！

寫在最後：

我從沒有怕過一款軟體，就是這版mysql,

我也從沒恨過一款軟體，也是這版mysql！

配置文件my.ini的路徑

ddl導致的對mdl互斥鎖的申請可能導致的系統卡死應該是所以mysql DBA都遇到過的坑？

卧槽了，5.7的是測試版不建議安裝！！？？我都弄了整個晚上加現在睡不著覺想解決辦法……

select * from table where Id&>1233 and lastModifyTime between "2016-11-11 00:00:00" and "2016-11-23" order by Id limit 1000

id是自增主鍵，lastModifyTime有索引

數據量三千萬

生產環境會超時

就問你怕不怕

1、沒有好的degub工具

2、關於null值的坑

這是源數據 select * from Persons

這是查詢語句 SELECT * FROM Persons WHERE LastName &<&>"測試"

查詢結果：

兩條空值消失了，一條保留

新手求解

1.對時間類型做ifnull，出來的結果是字元串

2.insert的內容有非法utf8編碼，不報錯，但記錄也沒有插入到表裡