mysql 常見sql語句總結和踩坑提醒

常用的但容易忘的：

1. 如果有主鍵或者唯一鍵衝突則不插入：insert ignore into
2. 如果有主鍵或者唯一鍵衝突則更新,注意這個會影響自增的增量：INSERT INTO room_remarks(room_id,room_remarks) VALUE(1,"sdf") ON DUPLICATE KEY UPDATE room_remarks="234"
3. 如果有就用新的替代，values如果不包含自增列，自增列的值會變化： REPLACE INTO room_remarks(room_id,room_remarks) VALUE(1,"sdf")
4. 備份表：CREATE TABLE user_info SELECT * FROM user_info
5. 複製表結構：CREATE TABLE user_v2 LIKE user
6. 從查詢語句中導入：INSERT INTO user_v2 SELECT * FROM user或者INSERT INTO user_v2(id,num) SELECT id,num FROM user
7. 連表更新：UPDATE user a, room b SET a.num=a.num+1 WHERE a.room_id=b.id

鎖相關(作為了解，很少用)

1. 共享鎖： select id from tb_test where id = 1 lock in share mode;
2. 排它鎖： select id from tb_test where id = 1 for update

優化時用到

1. 強制使用某個索引： select * from table force index(idx_user) limit 2;
2. 禁止使用某個索引： select * from table ignore index(idx_user) limit 2;
3. 禁用緩存(在測試時去除緩存的影響)： select SQL_NO_CACHE from table limit 2;

查看狀態

1. 查看字符集 SHOW VARIABLES LIKE 'character_set%';
2. 查看排序規則 SHOW VARIABLES LIKE 'collation%';

SQL編寫注意

1. where語句的解析順序是從右到左，條件儘量放where不要放having
2. 採用延遲關聯(deferred join)技術優化超多分頁場景，比如limit 10000,10,延遲關聯可以避免回表
3. distinct語句非常損耗性能，可以通過group by來優化
4. 連表儘量不要超過三個表

踩坑

1. 如果有自增列，truncate語句會把自增列的基數重置為0，有些場景用自增列作為業務上的id需要十分重視
2. 聚合函數會自動濾空，比如a列的類型是int且全部是NULL，則SUM(a)返回的是NULL而不是0
3. mysql判斷null相等不能用“a=null”,這個結果永遠為UnKnown，where和having中,UnKnown永遠被視為false，check約束中，UnKnown就會視為true來處理。所以要用“a is null”處理

閱讀更多 程序員界的彭于晏 的文章

關鍵字: SQL 主鍵 唯一