SQL COOKLIST：写SQL中的习惯&语法&暗坑

     O'REILLY有若干以“COOKBOOK”命名的“动物书”，讲的是写一些语言代码时被称作“语法糖”的黑科技。“语法糖”是神马不好形容，但物如其名，就是一些甜甜的东西。这篇博文正是启发于此。工作中几乎每天都要写SQL，每天也在学习SQL，所以就决定把与SQL相关的“语法糖”记录下来。因为只是一篇博文还远远达不到“BOOK”的级别，所以题目只能以“COOKLIST”命名了。文章内容仅仅代表个人的学识水平层次，个人也只有MySQL数据库工作经验，所以可能会有值得商榷的地方，只能just for reference。另外，这将是一篇不断补充、更新和修正的文章。因为内容很多而又总会想到和遇到新的可以写进来的东西。我暂时简单将内容分成“习惯篇”、“语法篇”和“暗坑篇”三个部分，分别收纳SQL中的好习惯、语法糖以及容易出问题的地方。每一部分从“0”开始计序数往下写，每一条尽量都会举个栗子，除非一眼能懂或者由于时间原因后期会再补上。

【习惯篇】

0.自命名名称使用小写

     自命名名称包含的内容很多，包括库名、表名、字段名、索引名、外键名以及自定义函数（udf）等等，即自己起名称的命名空间。使用小写最大的好处是方便平台迁移（Windows文件系统不区分大小写；Unix/Linux文件系统区分大小写）。另外就是看着习惯，不别扭。与此同时，最好的做法是SQL语句统一使用大写。这虽然根本没有什么实际益处，因为SQL语句是的的确确和明明确确不区分大小写的。那么，这有什么用啊？一是如果你遵守自命名名称使用小写（绝大多数人还是习惯这种习惯的）而又遵守SQL语句使用大写的话，在盯着满屏的SQL看时，会很容易定位哪些部分是自定义名称，哪些部分是SQL语法。二是纯粹的美观。

1.自命名名称按系统或功能类别等统一规划命名

     统一命名的好处是，当项目里的每个人都明白和认可这种命名规则后，就会极大地减轻沟通成本。如下示例是一种“主体_动作_说明”三段式的命名规则。其中，相同的主体使用同一个前缀，中间部分是具体操作，后接一个说明作为后缀。如果作为表名的话，可以一目了然不用看表注释就知道表里面记录的数据；而把表名从头到尾翻译一遍也就可以作为表名注释了。

user_register_record
user_request_detail
user_activity_statistics
client_login_record
client_logout_record
client_update_record

2.自命名名称尽量不要用数据库系统保留字符

     数据库系统保留字符包括SQL语句函数、数据库服务器系统变量以及标点符号等。使用保留字符总是会有一些潜在的识别问题，或明或暗，而一旦出现的时候可能会很摸不到头绪。使用保留字符的时候，就需要额外使用反单引号“`”来将其括起来，这就是一个问题。关于反单引号的使用，参考博文http://blog.csdn.net/sweeper_freedoman/article/details/52550743。

3.表名前面带上库名

     如果单条SQL是操作一台数据库服务器里面的多个Schema，那表名前面带上库名就是必须的；如果单条SQL只是操作一个Schema，虽然带不带库名不是必须的，但如果带上库名，就可以直接在一个连接会话的session里面操作该台数据库服务器里面的所有Schema，而不用来回切换。

4.写好注释

     DDL里的注释要详尽，SQL语句里必要时也不要吝惜写一写注释。写注释和写好注释永远都是一个好习惯。DDL里的注释很是必要的，这具有文档的成分。

CREATE TABLE `user_blacklist_record` (
`user_id` INT(10) UNSIGNED NOT NULL COMMENT '用户id(关联user_register_record表PK)',
`black_user_id` INT(10) UNSIGNED NOT NULL COMMENT '黑名单用户id(关联user_register_record表PK)',
`ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`user_id`, `black_user_id`)
)
COMMENT='{"表名" : "黑名单表", "创建人 / 负责人" : "蛙鳜鸡鹳狸猿", "创建时间" : "2016-12-06", "用途" : "记录用户的黑名单用户", "备注" : "o(>﹏<)o YaMieDie o(>﹏<)o"}'
;

5.少写和不写一行式SQL

     SQL语句不论长短从头到尾都是一行这是一种很不好的习惯。首先，自己第二次查看以及别人看的时候很费劲；其次，改起来需要找来找去很不方便；最后，我们显示器的分辨率总是有限的。一种理想的做法是根据SQL句首关键字断行。SQL句首关键字就是可以作为单独一句话开头的SQL。例如SELECT、FROM、JOIN、WHERE、GROUP以及ORDER等。必要时，SQL当然也像其他程序语言一样，可以通过缩进来显示逻辑而加强可读。

6.少写和不写长SQL

     长SQL在可读性、维护以及性能方面都存在问题。长SQL的处理办法总的来讲就是化繁为简，分解成一句句简单的SQL。可选的简化方式包括：使用自定义函数或session级别的内存表，使用中间表或统计表，编程语言后期处理等。

7.每张表都最好至少有一个主键

     主键（PK）可以算是关系型数据库核心思想的体现，是一条记录的唯一标识。同样地，对主键进行查询操作的SQL性能是最高的，因为主键数据页存放的是整条记录的完整数据。

8.数值类型是首选的数据类型

     从TINYINT到INT再到BIGINT以及BIT（已经很少用到了）的数值类型是最理想的数据类型。毕竟，数字就是辣么“原生态”。不论是存储、读取以及更新等数据方面的操作，数值类型的效率都会比其他更高一筹。MySQL优化器对数值类型的优化也是最有效率的。一些在可知范围内的字符串类型可以直接存成数值类型或者使用枚举（ENUM）。与此相关最直接的一个栗子就是有关IP数据的存储。IP数据最理想的方式是存储成数值类型（INT(10) UNSIGNED），方法是借助INET_ATON和INET_NTOA两个函数：INET_ATON()存储；INET_NTOA()读取。

mysql> SELECT INET_ATON('127.0.0.1');
+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT INET_NTOA(2130706433);
+-----------------------+
| INET_NTOA(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+
1 row in set (0.00 sec)

【语法篇】

0.快速查看系统变量

     在连接会话里面直接查询。

/*show variables regexp 'log' and turned on*/
SHOW VARIABLES WHERE Variable_name LIKE '%log%' AND Value = 'ON';

1.灵活使用MySQL系统自带的元数据库“information_schema”

     “information_schema”里面几乎有你所需要的关于你所使用的这台MySQL数据库服务器所使用数据的所有信息。里面的“TABLES”、“COLUMNS”、“PROCESSLIST”以及“STATISTICS”等表在数据库维护工作中，是形影不离和爱不释手的。很早的一篇博文就有使用“TABLES”表的一个栗子，http://blog.csdn.net/sweeper_freedoman/article/details/51137181。因其太实用了，忍不住想再多举几个栗子。

/*generate kill statement*/
SELECT CONCAT('KILL ', p.ID, ';')
FROM information_schema.`PROCESSLIST` p
WHERE p.`USER` = 'readuser';

/*select exe DML*/
SELECT p.ID, p.DB, p.COMMAND, p.TIME, p.STATE, p.INFO
FROM information_schema.`PROCESSLIST` p
WHERE p.INFO LIKE 'INSERT%' OR p.INFO LIKE 'UPDATE%';

2.快速批DML

     磁盘I/O和索引都会影响写入的性能，如果有大量DML尤其是INSERT语句需要执行，将其封装进一个事务并暂时关闭索引可以实现快速写入。当然更理想的是放入存储过程去调用。

-- do batch DML
ALTER TABLE homework_attention_cache DISABLE KEYS;

delimiter |||
START TRANSACTION;  
  
/*YOUR DML HERE*/

COMMIT |||
delimiter ;

ALTER TABLE homework_attention_cache ENABLE KEYS;

3.字符串匹配时最左匹配用LIKE&其他用REGEXP

     字符串匹配时，因为MySQL默认的BTREE（B+TREE）索引是最左匹配，所以像“str%”这种使用LIKE是可以走索引的。其他形式使用REGEXP，是走扩展正则表达式（ERE）的逻辑。

4.记录级别的清空用DELETE&表级别的清空用TRUNCATE

     如题。具体参考http://blog.csdn.net/sweeper_freedoman/article/details/52553427。

5.重复记录通过嵌套自关联删除重复保留一条

     如题。具体参考http://blog.csdn.net/sweeper_freedoman/article/details/52821415。

6.频数统计

     如题。具体参考http://blog.csdn.net/sweeper_freedoman/article/details/52753183。

7.按字段分组查询符合条件记录

     如题。具体参考http://blog.csdn.net/sweeper_freedoman/article/details/52753163。

8.为查询生成一个自增长序列字段

     这在查询过程中或者查询结果里都是很实用的。查询过程中可以通过生成的自增长序列字段进行表与表之间的关联；查询结果中的自增长序列字段可以作为记录标识以及数据长度在导出来的文件中增强结果的可读性。

/*show information_schema tables*/
SELECT (@rowNO := @rowNO + 1) AS rowNO, t.TABLE_NAME
FROM information_schema.`TABLES` t, (SELECT @rowNO := 0) AS tmp_rowNO
WHERE t.TABLE_SCHEMA = 'information_schema';

9.使用UNION ALL横向合并多表查询结果行

     多表查询的结果集横向合并的时候，需要用到UNION或者UNION ALL。UNION是UNION DISTINCT的简写，即对结果集还要进行一次额外的去重操作；UNION ALL则是单纯地返回合并后的结果集，因此可以节省DISTINCT过程的性能。如果已经明确知道单个表返回的结果集是唯一的，或者对合并的结果集并没有UNIQUE需求，查询中使用UNION ALL即可。

10.简化你的DDL

     MySQL5.6引进了Online DDL，很多常规的DDL操作是走直接在原表修改的逻辑。而在此之前，则需要copy出来一张新表执行DDL，很明显这种操作是性能不友好的。为了让DDL在较新的版本中走在线修改的方式，需要将复合DDL拆分成单条DDL，参考https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-single-multi.html。

ALTER TABLE t1 ADD INDEX i1(c1), ADD UNIQUE INDEX i2(c2),
  CHANGE c4_old_name c4_new_name INTEGER UNSIGNED;    /*适用于5.6以前版本*/

↓↓

ALTER TABLE t1 ADD INDEX i1(c1);
ALTER TABLE t1 ADD UNIQUE INDEX i2(c2);
ALTER TABLE t1 CHANGE c4_old_name c4_new_name INTEGER UNSIGNED NOT NULL;    /*适用于5.6及以后版本*/

11.SQL语句备份表

     表的备份有很多方法，最常用的当然是mysqldump。但很多时候可能需要直接通过SQL备份，可以走一个异库写入的简单逻辑（https://github.com/Bilery-Zoo/Database-Keeper-Relevant/blob/master/table_crossdatabase_backup.sql）。

DROP TABLE IF EXISTS tar_database/*target database*/.table_backup/*table to backup*/;
CREATE TABLE tar_database/*target database*/.table_backup/*table to backup*/ LIKE src_database/*source database*/.table_backup/*table to backup*/;
INSERT INTO tar_database/*target database*/.table_backup/*table to backup*/ SELECT * FROM src_database/*source database*/.table_backup/*table to backup*/;

12.MySQL命令行调用执行Linux操作系统命令

     通过MySQL自带的“system”命令，参考https://dev.mysql.com/doc/refman/5.7/en/mysql-commands.html。非常实用的功能。

mysql> system uname -a;
Linux ubuntu 4.4.0-78-generic #99-Ubuntu SMP Thu Apr 27 15:29:09 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

13.借助操作系统硬连接快速DROP TABLE
     小一点的表直接用DROP TABLE语句删除，但是大一点的表用DROP语句操作视硬件性能等限制就可能需要等待，因为MySQL会等到整张表（文件）都删除完才会释放进程。这时候可以借助操作系统的硬连接加快删表操作。

mysql> show variables like 'datadir';  
+---------------+-----------------+  
| Variable_name | Value           |  
+---------------+-----------------+  
| datadir       | /var/lib/mysql/ |  
+---------------+-----------------+  
1 row in set (0.24 sec)  
   
mysql> system ls /var/lib/mysql/benchmarker/
columns.frm  columns.ibd  db.opt  tables.frm  tables.ibd  
mysql> system ln /var/lib/mysql/benchmarker/columns.ibd /var/lib/mysql/benchmarker/columns.ibd.hl  
mysql> system ls -lh /var/lib/mysql/benchmarker  
总用量 15M  
-rw-r----- 1 mysql mysql  14K 7月  10 00:40 columns.frm  
-rw-r----- 2 mysql mysql 7.0M 7月  10 00:40 columns.ibd  
-rw-r----- 2 mysql mysql 7.0M 7月  10 00:40 columns.ibd.hl  
-rw-r----- 1 mysql mysql   61 6月  22 01:24 db.opt  
-rw-r----- 1 mysql mysql  18K 7月  10 00:40 tables.frm  
-rw-r----- 1 mysql mysql  96K 7月  10 00:40 tables.ibd  
mysql> drop table benchmarker.columns;
Query OK, 0 rows affected (0.11 sec)  
  
mysql> system ls -lh /var/lib/mysql/benchmarker | grep columns  
-rw-r----- 1 mysql mysql 7.0M 7月  10 00:40 columns.ibd.hl  
mysql> show tables;
+-----------------------+  
| Tables_in_benchmarker |  
+-----------------------+  
| tables                |  
+-----------------------+  
1 row in set (0.00 sec)  
  
mysql> system rm -f /var/lib/mysql/benchmarker/columns.ibd.hl

【暗坑篇】

0.NULL != 0

     在数值计算中，如果忽视存储的NULL值而进行计算，可能会得到失真的结果，因为NULL值的计算结果返回NULL。

mysql> SELECT NULL + 11;
+-----------+
| NULL + 11 |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT NULL * 11;
+-----------+
| NULL * 11 |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

     为此，可能需要IFNULL进行一次额外判定。

mysql> SELECT IFNULL(NULL, 0) + 11;
+----------------------+
| IFNULL(NULL, 0) + 11 |
+----------------------+
|                   11 |
+----------------------+
1 row in set (0.00 sec)

1.NULL != ''

     NULL是还没有值，而''是空字符串，是有值的。虽然比较绕，但二者是不一样的。

mysql> SELECT IFNULL('', 11);
+----------------+
| IFNULL('', 11) |
+----------------+
|                |
+----------------+
1 row in set (0.00 sec)

2.CONCAT('', NULL, '') = NULL

     只要出现一个NULL值，CONCAT拼接操作就会打回原形（NULL）。参考http://blog.csdn.net/sweeper_freedoman/article/details/54577186。

mysql> SELECT CONCAT('My', 'SQL', NULL);
+---------------------------+
| CONCAT('My', 'SQL', NULL) |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT('My', 'SQL');
+---------------------+
| CONCAT('My', 'SQL') |
+---------------------+
| MySQL               |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT 'My' 'SQL';
+-------+
| My    |
+-------+
| MySQL |
+-------+
1 row in set (0.00 sec)

3.SQL不区分大小写但是字符串区分大小写

     这一点虽然是很明晰的，但可能粗心大意之下就会忽视，尤其是在一些字符串操作函数中。所以，'i' != 'I'、'love' != 'LOVE'、'mysql' != 'MySQL'。

4.查询中的字符串区分大小写需要额外声明（COLLATE）

     如果在建表时没有对字段格式进行binary设置（一般不会额外设置），查询中需要区分记录中的大小写，就需要对该字段进行COLLATE指定。参考https://dev.mysql.com/doc/refman/5.7/en/charset-collate.html。

mysql> SELECT COUNT(*) AS cnt
    -> FROM information_schema.`COLUMNS` c
    -> WHERE c.TABLE_CATALOG REGEXP 'd' COLLATE utf8_bin;
+------+
| cnt  |
+------+
| 3138 |
+------+
1 row in set (0.10 sec)

mysql> SELECT COUNT(*) AS cnt
    -> FROM information_schema.`COLUMNS` c
    -> WHERE c.TABLE_CATALOG REGEXP 'D' COLLATE utf8_bin;
+-----+
| cnt |
+-----+
|   0 |
+-----+
1 row in set (0.04 sec)