MySQL数据库，4个写SQL的好习惯，让你不再给人挖坑

落叶说Mysql数据库运维 2020-11-05

431

简介

无论是开发人员，还是数据库运维人员，一定要遵守规范，养成好的写SQL习惯，会让你工作更加顺风顺水。

举例表结构

以下所有的SQL涉及的表结构如下所示

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)
复制

习惯一：INSERT语句标明对应的字段名称

在代码里，写insert语句，一定要写要插入数据的字段名称。

不好的习惯，不写字段名称

复制

insert into t_test_1 values(1,'name1','name2',1);
复制

好的习惯，明确写明要插入数据的字段名称复制

复制

insert into t_test_1(id,name,name2,status) values(1,'name1','name2',1);
复制

可是为什么不写字段名称，是不好的习惯呢，在这里举一个实际的案例：假如明天晚上20点应用要做变更上线，这次上线之前，需要在t_test_1表里添加字段（'pay_type'），正常上线流程，肯定是DBA先做表结构变更，然后再做应用上线。好了，下面来做一次变更操作

1.表（t_test_1）结构变更

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)
复制


复制

2.表结构添加好字段之后，应用还没有上线前，会发生什么呢？

复制

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

看到了吧，由于t_test_1表添加了一个新字段，这个时候执行insert语句，就会报错了，这个时候，估计会一大堆的告警了，排查之后，只能删除字段，回滚上线了，而且删除字段，会再次导致业务堵塞，一个线上故障就这么发生了，做为一个开发人员，你还敢这么写insert语句吗？

避免隐式转换

写SQL语句时，一定要注意隐式转换，这不仅仅会导致查询性能问题，而且会导致查询结果不正确。导致查询性能问题，估计大部分朋友有所了解，可是导致查询结果不正确，估计有一部分朋友不太了解。下面就来举例说明一下。

1.测试SQL语句

select * from t_test_1 where name2=1001
复制

来看看测试SQL语句的执行计划

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)
复制

看到了吧，Cannot use range access on index 'idx_t_test_1_name2' due to type or collation conversion on field 'name2' ，因为隐士转换，导致无法使用索引。这个SQL不仅仅只有这个问题，还有一个更严重的问题，查询结果不正确。

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)
复制

复制

在这里多查询了一条记录（id=12），所以好的习惯是如果字段定义成字符串，则查询条件一定要带单引号

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)
复制

每个表必须要有主键

数据库上每张表，必须要设置主键，这个非常重要，如果不设置主键，会导致表中数据记录重复，导致主从复制延迟。

禁止用select *

禁止用select * ，这个很多开发人员知晓，避免用select * 可以节省资源、减少网络开销。可是能节省多少呢，没有一个概念，这里来举个极端的例子。

假设有一张带有一个blob字段的表，这个blob字段存储了大约500M的数据，如果前端用分页展示，每页显示20条记录，但是不需要显示这个blob字段信息，但是开发人员用select * ，导致向前端返回20条带有blob字段信息，这20条记录总大小：20*500M=10G，看到了，返回了10G的数据，这个数据非常有可能直接将后端应用程序就给弄内存溢出。

mysql

文章转载自落叶说Mysql数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。