Ⅰ sql踩坑記錄-Update多個欄位
最近遇到了把我弊磨坑慘的一個update語句：
update apps set owner = �' and owner_name = 'xiaoming' where owner_code = �' and owner_name = 'lihua'
在碼昌MySQL裡面update一條記錄，語法都正確的，但記錄並沒有被更新...
剛遇到這個問題的時候，我拿到這條語句直接在測試庫裡面執行了好幾次，發現確實有問題。
測試SQL： update apps set owner = �' and owner_name = '李四' where owner_code = �' and owner_name = '張三'
執行之前的記錄是這樣的：
執行之後的記錄是這樣的：
可以看到，結果並不像這位開發同學說的「好像沒有效果」，實際上是有效果的：
看起來，語法是完全沒有問題，翻了翻MySQL官方文檔的update語法遲卜扒：
看到assignment_list的格式是以逗號分隔的col_name=value列表，一下子豁然開朗，開發同學想要的多欄位更新語句應該這樣寫：
倒回去再重試驗一把：
果然，這下得到了想要的結果！
小結 ： 在一條UPDATE語句中，如果要更新多個欄位，欄位間不能使用「AND」，而應該用逗號分隔。
後記 ： 後面等有空的時候，又回過頭來看了一下，為什麼使用「AND」分隔的時候，會出現owner_code=0的奇怪結果？多次嘗試之後發現：
等價於：
而 (�' and owner_name='李四') 是一個邏輯表達式，而這里不難知道owner_name並不是『李四』。
因此，這個邏輯表達式的結果為 false ， false在MySQL中等價於0！
Ⅱ 怎麼跳出MySQL的10個大坑
有兩種方法，一種方法使用mysql的check table和repair table 的掘舉遲sql語句，另一種方法是使用MySQL提供的多判李個myisamchk, isamchk數據檢測恢復工具。前者使用起來比較簡便。推薦使用。
1. check table 和 repair table
登陸mysql 終端：
mysql -uxxxxx -p dbname
check table tabTest;
如果出現的結果說Status是OK，則不用修復，如果有Error，可以用：
repair table tabTest;
進行修復，修復之後可以在用check table命令來進行檢查。在答局新版本的phpMyAdmin裡面也可以使用check/repair的功能。
2. myisamchk, isamchk
其中myisamchk適用於MYISAM類型的數據表，而isamchk適用於ISAM類型的數據表。這兩條命令的主要參數相同，一般新的系統都使用MYISAM作為預設的數據表類型，這里以myisamchk為例子進行說明。當發現某個數據表出現問題時可以使用：
myisamchk tablename.MYI
進行檢測，如果需要修復的話，可以使用：
myisamchk -of tablename.MYI
關於myisamchk的詳細參數說明，可以參見它的使用幫助。需要注意的時在進行修改時必須確保MySQL伺服器沒有訪問這個數據表，保險的情況下是最好在進行檢測時把MySQL伺服器Shutdown掉。
Ⅲ 怎麼跳出MySQL的10個大坑
一種方碧姿指法使用mysql的check table和repair table 的sql語句
另一種方法是使用MySQL提悔配供的多個myisamchk, isamchk數據檢測恢復工具。前冊數者使用起來比較簡便。
Ⅳ 經驗分享：8種常見SQL錯誤用法
1、LIMIT 語句
分頁查詢是最常用的場景之一，但也通常也是最容易出問題的地方。比如對於下面簡單的語句，一般 DBA 想到的辦法是在 type, name, create_time 欄位上加組合索引。這樣條件排序都能有效的利用到索引，性能迅速提升。
好吧，可能90%以上的 DBA 解決該問題就到此為止。但當 LIMIT 子句變成 「LIMIT 1000000,10」 時，程序員仍然會抱怨：我只取10條記錄為什麼還是慢？
要知道資料庫也並不知道第1000000條記錄從什麼地方開始，即使有索引也需要從頭計算一次。出現這種性能問題，多數情形下是程序員偷懶了。
在前端數據瀏覽翻頁，或者大數據分批導出等場景下，是可以將上一頁的最大值當成參數作為查詢條件的。SQL 重新設計如下：
在新設計下查詢時間基本固定，不會隨著數據量的增長而發生變化。
2、隱式轉換
SQL語句中查詢變數和欄位定義類型不匹配是另一個常見的錯誤。比如下面的語句：
其中欄位 bpn 的定義為 varchar(20)，MySQL 的策略是將字元串轉換為數字之後再比較。函數作用於表欄位，索引失效。
上述情況可能是應用程序框架自動填入的參數，而不是程序員的原意。現在應用框架很多很繁雜，使用方便的同時也小心它可能給自己挖坑。
3、關聯更新、刪除
雖然 MySQL5.6 引入了物化特性，但需要特別注意它目前僅僅針對查詢語句的優化。對於更新或刪除需要手工重寫成 JOIN。
比如下面 UPDATE 語句，MySQL 實際執行的是循環/嵌套子查詢（DEPENDENT SUBQUERY)，其執行時間可想而知。
執行計劃：
重寫為 JOIN 之後，子查詢的選擇模式從 DEPENDENT SUBQUERY 變成 DERIVED，執行速度大大加快，從7秒降低到2毫秒。
執行計劃簡化為：
4、混合排序
MySQL 不能利用索引進行混合排序。但在某些場景，還是有機會使用特殊方法提升性能的。
執行計劃顯示為全表掃描：
由於 is_reply 只有0和1兩種狀態，我們按照下面的方法重寫後，執行時間從1.58秒降低到2毫秒。
5、EXISTS語句
MySQL 對待 EXISTS 子句時，仍然採用嵌套子查詢的執行方式。如下面的 SQL 語句：
執行計劃為：
去掉 exists 更改為 join，能夠避免嵌套子查詢，將執行時間從1.93秒降低為1毫秒。
新的執行計劃：
6、條件下推
外部查詢條件不能夠下推到復雜的視圖或子查詢的情況有：
如下面的語句，從執行計劃可以看出其條件作用於聚合子查詢之後：
確定從語義上查詢條件可以直接下推後，重寫如下：
執行計劃變為：
關於 MySQL 外部條件不能下推的詳細解釋說明請參考文章：
7、提前縮小范圍
先上初始 SQL 語句：
該SQL語句原意是：先做一系列的左連接，然後排序取前15條記錄。從執行計劃也可以看出，最後一步估算排序記錄數為90萬，時間消耗為12秒。
由於最後 WHERE 條件以及排序均針對最左主表，因此可以先對 my_order 排序提前縮小數據量再做左連接。SQL 重寫後如下，執行時間縮小為1毫秒左右。
再檢查執行計劃：子查詢物化後（select_type=DERIVED)參與 JOIN。雖然估算行掃描仍然為90萬，但是利用了索引以及 LIMIT 子句後，實際執行時間變得很小。
8、中間結果集下推
再來看下面這個已經初步優化過的例子(左連接中的主表優先作用查詢條件)：
那麼該語句還存在其它問題嗎？不難看出子查詢 c 是全表聚合查詢，在表數量特別大的情況下會導致整個語句的性能下降。
其實對於子查詢 c，左連接最後結果集只關心能和主表 resourceid 能匹配的數據。因此我們可以重寫語句如下，執行時間從原來的2秒下降到2毫秒。
但是子查詢 a 在我們的SQL語句中出現了多次。這種寫法不僅存在額外的開銷，還使得整個語句顯的繁雜。使用 WITH 語句再次重寫：
總結
資料庫編譯器產生執行計劃，決定著SQL的實際執行方式。但是編譯器只是盡力服務，所有資料庫的編譯器都不是盡善盡美的。
上述提到的多數場景，在其它資料庫中也存在性能問題。了解資料庫編譯器的特性，才能避規其短處，寫出高性能的SQL語句。
程序員在設計數據模型以及編寫SQL語句時，要把演算法的思想或意識帶進來。
編寫復雜SQL語句要養成使用 WITH 語句的習慣。簡潔且思路清晰的SQL語句也能減小資料庫的負擔 。
Ⅳ 記一次MySQL Group by 的坑
表結構如下：
業務需要將表中重復數據刪除，所以需要按照 組合唯一索引鍵篩選出重復的數據進行刪除。SQL如下：
表中有符合索引 KEY column1_column2_index ( column1 , column2 )
sql語句 Group by 也是按照最左匹配原則順序寫的 group by 的欄位，但是每次執行SQL耗時都是好幾十秒
explain 該 sql 發現，並沒有走表中存在的復合索引，而是直接走的 File sorted（文件排序）；group by 語句其實是頃虛有要先排序再分組念悉的；
問題的關鍵定位到沒有沒有命中表中的復合索引，那為何 group by 欄位前兩個就是復合索引，只是最後兩個不是，為何沒有走索引呢？不是索引只要滿足最左匹配原則就可以命中嗎？
分析後發現，索引可以用在兩個地方，1 被用於提高WHERE條件的數據行匹配或者執行聯結操作時匹配其它表的數據行的搜索速度。2 快速地執行ORDER BY和GROUP BY語句的排序和分組操作。
本處就是可以使用索引做排序使用，而避免文件排序；此處要命中索引，走索引排序，必須要表中有一個復合索引包含 group by 的所有欄位且順序一致；
網上有部分博客說 group by 自帶的排序和 order by 排序，走不走索引的規則是一樣的，這里本人測試了一下，添加 group by 後面所有順序欄位的復合仔乎乎索引對 group by 的查詢時間有直接的影響，從 30多秒 優化到 3秒；
但是對如下SQL 的執行時間也有影響，但是遠遠沒有對group by 的影響大，如下sql，添加 order by 的全索引後 只能從30多秒優化到 10 多秒
select column2 from am_cm_relationship order by column1,column2,column3,column4 ;
Ⅵ mac MySQL的那些坑之mysql: command not found
第一次安裝好MySQL後, 打開終端准備輸入 mysql -uroot -p 發現報錯:
按照網址上的命令在終端輸入了一下,
然後再輸入 mysql -uroot -p 發現又報錯了.
系統偏好設置< MySQL < Start MySQL Server
顯示如圖就說明已經開啟了SQL服務:
好吧, 接下來又激鉛接著輸入 mysql -uroot -p 發現終於允許敲密碼了!
登錄初喊鉛歲始密碼, 然後立馬換密碼啊! 怎麼更換密碼, 請看我的另一篇文章~
]( http://www.jianshu.com/p/0f23874705be )
然後就可以使用mysql, 但是我突然想起一個事
果然不出我所料. 也不能每次都輸入上面的兩個語句吧.
學習就是這樣, 從一個坑跳到另一個坑~
怎麼樣能夠一勞永逸呢? 可以去修改環境變數.
STEP 1
輸入如下命令修改home目錄下的 .bash_profile 文件，如果之前沒有進行過環境變數的配置，那麼該文件將是空白的無內容，執行如下命令
STEP 2
並將下面這句話👇 原封不動 復制到 .bash_profile 文件中
記得 保存 (command+S), 然後關閉.
這鄭睜一步完成之後徹底關閉命令行
重新打開就可以使用的MySQL命令了.
然而 , 由於我是新電腦, 根本就沒有bash_profile文件, 所以報了這個錯:
自然就是創建一個 .bash_profile 文件了.
打開終端開始輸入:
STEP 1
*注: * 進入HOME文件夾中
STEP 2
*注: * 創建一個 .bash_profile 文件
STEP 3
然後就與上面相同, 打開.bash_profile 文件
STEP 4
將下面這句話👇 原封不動 復制到 .bash_profile 文件中, 記得 保存 (command+S), 然後關閉.
希望mac MySQL的那些坑這一系列文章能夠幫助大家盡快入門~~
Ⅶ Mysql NULL之踩坑記
在開發過程中踩過的坑，在此做下記錄，謹防下次入坑。
例：表table
執行sql 如下
得到結帆皮果如下
第一列state為null的也態余差被過濾掉
解決辦法1
解決辦法2
踩坑原因
大多數編程語言都是基於二值邏輯的毀卜，即邏輯值只有true、false 兩個。而sql語言採用的是三值邏輯，除了true、false 之外還有一個「unknown」。
即所有值與NULL比較都返回不確定
Ⅷ SQL Server datatime類型精度的坑
datetime類型的精度為 ：千分之三秒（3.33毫秒、0.00333秒）。所有的datetime類型的值在顯示、處理時 有所調整。
即會圓整到幾個特殊的毫秒個位值：0、3、7：如：(9、0、1） 會引起進0調整；（5、6、7、8）引起7調整；（2、3、4）引起3調整。
做一個小測試
你會發現 @dt 的值變為了 2020-07-01 00:00:00.000
那麼查詢的坑就來了。
你明明想只查 2020.6.30 出生的數據。 where birthday>='2020-06-30 00:00:00.000' and birthday<='2020-06-30 23:59:59.999'
結果7.1號出生的也查詢出來了。
要更精確的秒數位數 可使用 datetime2類型。datatime2 秒支持到7位小數
查詢出來dt的結果就是 2020-06-30 23:59:59.9990000