在MySQL數據庫，4個寫SQL的好習慣，讓你不再給人挖坑

簡介

無論是開發人員，還是數據庫運維人員，一定要遵守規範，養成好的寫SQL習慣，會讓你工作更加順風順水，讓你不再給人挖坑。

舉例表結構

以下所有的SQL涉及的表結構如下所示

<code>mysql> show create table t_test_1\G;
*************************** 1. row ***************************
       Table: t_test_1
Create Table: CREATE TABLE `t_test_1` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `name2` char(15) NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)/<code>

習慣一：INSERT語句標明對應的字段名稱

在代碼裡，寫insert語句，一定要寫要插入數據的字段名稱。

不好的習慣，不寫字段名稱

<code>insert into t_test_1 values(1,'name1','name2',1);/<code>

好的習慣，明確寫明要插入數據的字段名稱

<code>insert into t_test_1(id,name,name2,status) values(1,'name1','name2',1);/<code>

可是為什麼不寫字段名稱，是不好的習慣呢，在這裡舉一個實際的案例：假如明天晚上20點應用要做變更上線，這次上線之前，需要在t_test_1表裡添加字段（'pay_type'），正常上線流程，肯定是DBA先做表結構變更，然後再做應用上線。好了，下面來做一次變更操作

1.表（t_test_1）結構變更

<code>mysql> alter table t_test_1 add pay_type smallint(1) not null default '0';
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t_test_1\G;
*************************** 1. row ***************************
       Table: t_test_1
Create Table: CREATE TABLE `t_test_1` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `name2` char(15) NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT '0',
  `pay_type` smallint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)/<code>

2.表結構添加好字段之後，應用還沒有上線前，會發生什麼呢？

<code>mysql> insert into t_test_1 values(2,'name2','name2',2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1/<code>

看到了吧，由於t_test_1表添加了一個新字段，這個時候執行insert語句，就會報錯了，這個時候，估計會一大堆地告警了，排查之後，只能刪除字段，回滾上線了，而且刪除字段，會再次導致業務堵塞，一個線上故障就這麼發生了，做為一個開發人員，你還敢這麼寫insert語句嗎？

避免隱式轉換

寫SQL語句時，一定要注意隱式轉換，這不僅僅會導致查詢性能問題，而且會導致查詢結果不正確。導致查詢性能問題，估計大部分朋友有所瞭解，可是導致查詢結果不正確，估計有一部分朋友不太瞭解。下面就來舉例說明一下。

1.測試SQL語句
select * from t_test_1 where name2=1001

來看看測試SQL語句的執行計劃

<code>mysql> explain select * from t_test_1 where name2=1001;
+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys      | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_test_1 | NULL       | ALL  | idx_t_test_1_name2 | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.02 sec)

mysql> 
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                         |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'idx_t_test_1_name2' due to type or collation conversion on field 'name2'                                                                                                                                        |
| Warning | 1739 | Cannot use range access on index 'idx_t_test_1_name2' due to type or collation conversion on field 'name2'                                                                                                                                      |
| Note    | 1003 | /* select#1 */ select `testdb`.`t_test_1`.`id` AS `id`,`testdb`.`t_test_1`.`name` AS `name`,`testdb`.`t_test_1`.`name2` AS `name2`,`testdb`.`t_test_1`.`status` AS `status` from `testdb`.`t_test_1` where (`testdb`.`t_test_1`.`name2` = 1001) |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)/<code>

看到了吧，Cannot use range access on index 'idx_t_test_1_name2' due to type or collation conversion on field 'name2' ，因為隱士轉換，導致無法使用索引。這個SQL不僅僅只有這個問題，還有一個更嚴重的問題，查詢結果不正確。

<code>mysql> select * from t_test_1 where name2=1001;
+----+--------+-------+--------+
| id | name   | name2 | status |
+----+--------+-------+--------+
|  1 | name1  | 1001  |      0 |
| 10 | name10 | 1001  |      0 |
| 11 | name11 | 1001  |      2 |
| 12 | name12 | 1001C |      3 |
+----+--------+-------+--------+
4 rows in set, 1 warning (0.01 sec)/<code>

在這裡多查詢了一條記錄（id=12），所以好的習慣是如果字段定義成字符串，則查詢條件一定要帶單引號

<code>mysql> select * from t_test_1 where name2='1001';
+----+--------+-------+--------+
| id | name   | name2 | status |
+----+--------+-------+--------+
|  1 | name1  | 1001  |      0 |
| 10 | name10 | 1001  |      0 |
| 11 | name11 | 1001  |      2 |
+----+--------+-------+--------+
3 rows in set (0.00 sec)/<code>

每個表必須要有主鍵

數據庫上每張表，必須要設置主鍵，這個非常重要，如果不設置主鍵，會導致表中數據記錄重複，導致MySQL數據庫的主從複製延遲。

禁止用select *

禁止用select * ，這個很多開發人員知曉，避免用select * 可以節省資源、減少網絡開銷。可是能節省多少呢，沒有一個概念，這裡來舉個極端的例子。

假設有一張帶有一個blob字段的表，這個blob字段存儲了大約500M的數據，如果前端用分頁展示，每頁顯示20條記錄，但是不需要顯示這個blob字段信息，但是開發人員用select * ，導致向前端返回20條帶有blob字段信息，這20條記錄總大小：20*500M=10G，看到了，返回了10G的數據，這個數據非常有可能直接將後端應用程序就給弄內存溢出。

關注

1.如果您喜歡這篇文章，請點贊+轉發。

2.如果您特別喜歡，請加關注。