第5章 开发技巧
本章将介绍一些和数据库相关的开发技巧。由于开发领域很广，这里只选取部分比较常见的小技巧。
5.1 存储树形数据
有时我们需要保存一些树形的数据结构，比如组织架构、话题讨论、知识管理、商品分类，这些数据存在一种递归关系，
很多研发人员想到的第一个解决方案往往是记录每个节点的父节点，例如以下的评论表。
CREATE TABLE comments ( comment_id int(10) NOT NULL, parent_id int(10) DEFAULT NULL, comment text NOT NULL, PRIMARY KEY (comment_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
如果采用这样的结构，当一篇帖子回复讨论的内容很多的时候，就需要编写复杂的代码递归检索很多记录，查询的效率就会很低。
如果数据量不大、讨论内容相对固定，数据的层次较少，那么采用这样的结构就会是简单的、清晰的，这种情况下此结构还是合适的；但如果数据量很大，查询就会变得很复杂。
下面介绍两种更通用，扩展性更好的解决方案：路径枚举和闭包表。
（1）路径枚举
对于如表5-1所示的表结构，可以增加一个字段path，用于记录节点的所有祖先信息。
记录的方式是把所有的祖先信息组织成一个字符串。
因为路径（path）字段包含了该节点的所有祖先信息，所以可以轻易地获取某个节点的所有祖先节点，
可以用程序先获取 path字符串，然后再使用切割字符串的函数处理得到所有的祖先节点。
如果要查找某个节点的所有后代，例如查找comment_id等于3的所有后代，可以使用如下的查询语句。
SELECT * FROM comments WHERE path LIKE ‘ 1/3/_%’ ;
如果要查找下一层子节点，可以使用如下的查询语句
SELECT * FROM comments WHERE path REGEXP “ ^1/3/[0-9]+/$” ;
插入操作也比较简单，只需要复制一份父节点的路径，并将新节点的ID值（comment_id）添加到路径末尾就可以了。
枚举路径的方式使得查询子树和祖先都变得更加简单，查看分隔符即可知道节点的层次，
虽然冗余存储了一些数据，应用程序需要额外增加代码以确保路径信息的正确性，但这种设计的扩展性更好，更能适应未来数据的不断增长。
表5-2中，仍然保留了parent_id列，是为了使一些操作更加方便，也可以用来校验路径信息是否正确。
（2）闭包表
闭包表也是一种通用的方案，它需要额外增加一张表，用于记录节点之间的关系。
它不仅记录了节点之间的父子关系，也记录了树中所有节点之间的关系。
使用如下命令语句新建表path
CREATE TABLE path ( ancestor int(11) NOT NULL, descendant int(11) NOT NULL, PRIMARY KEY (ancestor,descendant) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
ancestor表示祖先，descendant表示后代，存储的是comment_id值。
有了如表5-3所示的完整的节点间关系，查找后代节点、祖先节点也变得更容易，
比如，如果要统计comment_id等于3的所有后代（不包括其自身），可以直接搜索path表祖先是3的记录即可得到，搜索语句如下。
SELECT COUNT(*) FROM path WHERE ancestor=3 AND descendant <> 3;
为了更方便地查询直接父节点/子节点，可以增加一个path_length字段以表示深度，节点的自我引用path_length等于0，到它的直接子节点的path_length等于1，再下一层为2，以此类推。
如上所述的数据结构，新增了一个表，用于存储节点之间的信息，是一种典型的“以空间换时间”的方案，而且一个节点可以属于多棵树。
相对于路径枚举，闭包表的节点关系更容易维护。
其他的操作如删除、插入等这里不再赘述，有兴趣的读者可 以在网上查找“闭包表”的相关案例深入学习。
5.2 转换字符集
如果我们需要修改某个表的字符集，比如A表的字符集原来是gbk，现在要将其修改为utf8，一般有以下3种方法。
（1）直接在mysql命令行下完成
步骤如下
1）建立一个临时表B，字段类型和A一致，但字符集是utf8，即表定义中DEFAULTCHARSET=utf8。
2）INSERT INTO B SELECT * FROM A;
3）DROP TABLE A;
4）RENAME B TO A;
（2）使用mysqldump工具完成
首先导出数据，默认mysqldump导出的dump转储文件为utf8编码的文件，有删除表、创建表的语句。
然后修改dump转储文件，将创建表语句里的表或列的字符集定义修改为utf8。
最后重新导入此文件即可。
（3）使用ICONV命令转换文件编码
步骤如下
1）以gbk字符集导出数据，不导出表定义。 mysqldump -t -uroot -p database_name table_name1 table_name2 --default-character-set=gbk > a_gbk.sql
2）使用iconv命令转换文件编码，将其转换为utf8编码。 iconv -fgbk -tutf-8 a_gbk.sql > a_utf8.sql
3）修改文件中的相关字符集设置。 sed -i ‘ s/SET NAMES gbk/SET NAMES utf8/’ a_utf8.sql
4）删除旧表（table_name1，table_name2），新建表（table_name1，table_name2），注意新建的表应该是utf8字符集。
5）使用修改过的文件导入数据。 mysql -uroot -p database_name --default-character-set=utf8 < a_utf8.sql
在早期的数据库版本中还会有一种特殊情况，由于研发人员缺乏经验，选择了错误的数据库编码，采用latin1编码存储了中文数据。
因为特殊的字符集设置，比如客户端（character_set_client）、连接（character_set_connection）、结果 （character_set_results）的编码都是latin1编码，
这样从程序到数据库就不会做任何转换，而将中文编码（例如gbk）以latin1编码的方式进行存储。
也就是说，把每个汉字当成两个latin1字符进行存储，而且数据库发送结果的时候也是按照latin1的方式进行发送，
而我们的页面接收到数据之后则是以中文的编码方式进行显示，因此能正常地显示。
但是，这毕竟是一种错误的设置，数据存在重大隐患，解决方案是将latin1字符集转换为gbk字符集或utf8字符集。
如下是具体的转换步骤。
（1）latin1转gbk
1）导出数据库
mysqldump --default-character-set=latin1 -h xxx.xxx.xxx.xxx -u root -P 3306 -pxxxxxxxx db_name table_name > /usr/home/garychen/table_name.sql
2）修改table_name.sql
将/*!40101 SETNAMES latin1*/;改为/*!40101 SETNAMES gbk*/;
将DEFAULT CHARSET=latin1;改为DEFAULT CHARSET=gbk;
注意:不同版本的mysqldump修改时可能稍有出入，建议实际修改时再确认下。
3）导入数据库
mysql -uroot -pxxxxxxxx db_name < table_name.sql
（2）latin1转utf8
1）导出数据库，同上面的例子。
2）转换编码 iconv -t utf-8 -f gbk -c table_name.sql > table_name_u8.sql
注意：用latin1保存中文原本就是错误的做法，文件中存储的是错误的latin1编码，但实际上是正确的gbk编码，所以这里输入编码（-f）应为gbk。
3）修改table_name_u8.sql，使用vi或sed命令把latin1都改为utf8。
4）导入数据库 mysql -uroot -pxxxxxxxx db_name < table_name_u8.sql
5.3 处理重复值
表或结果集有时会包含重复记录，需要采用某种方法标识这些重复的记录并移除它们，以下示例将说明如何预防重复值， 以及如果存在重复记录时应如何移除它们。
（1）防止表中出现重复的记录
可以使用主键或唯一索引来防止出现重复的记录。
例如，下表person_tbl允许出现first_name和last_name组合相同的记录。
CREATE TABLE person_tbl ( first_name CHAR(20), last_name CHAR(20), sex CHAR(10));
可以设置（last_name，first_name）为主键，以确保不出现重复记录，语句如下。
CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name));
也可以设置唯一索引，来强制记录是唯一的，语句如下。
CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10) UNIQUE (last_name, first_name));
对于可能出现重复的记录，我们可以考虑使用INSERT IGNORE语句。
如果插入的记录并没有和现存的记录发生冲突，则正常插入之；如果有重复冲突，那么INSERT IGNORE将会告诉MySQL丢弃这条记录，且不报错。
如下面这个例子。
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES ( 'Jay', 'Thomas');
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES ( 'Jay', 'Thomas');
还可以考虑采用REPLACE语句，如果记录是新的，那么它等同于INSERT。如果插入的是一个重复的记录，那么新记录将会替换旧的记录。
mysql> REPLACE INTO person_tbl (last_name, first_name) VALUES ( 'Ajay', 'Kumar');
mysql> REPLACE INTO person_tbl (last_name, first_name) VALUES ( 'Ajay', 'Kumar');
综上所述，对于重复的记录，INSERT IGNORE仍然保留着现在的记录，丢弃新插入的记录。而REPLACE语句则会使用新的记录覆盖掉旧的记录。
（2）统计和识别重复值
如下语句将查询和计算表person_tbl中（last_name，first_name）组合有重复的记录的数量。
mysql> SELECT COUNT (*) AS repetitions, last_name, first_name FROM person_tbl GROUP BY last_name, first_name HAVING repetitions > 1;
（3）从结果集中消除重复记录
使用DISTINCT关键字即可从结果集中消除重复记录。
mysql> SELECT DISTINCT last_name, first_name FROM person_tbl ORDER BY last_name;
或者，也可以使用GROUP BY子句。
mysql> SELECT last_name, first_name FROM person_tbl GROUP BY (last_name, first_name);
（4）删除表中的重复记录
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
Mysql> ALTER TABLE tmp RENAME TO person_tbl;
还有一个不为人知的技巧，可以直接在一个有重复记录的表上加上主键或唯一索引，可使用ALTER IGNORE语句，命令如下。
mysql> ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);
可以使用如上的方法消除重复记录，并且确保以后都有唯一约束。
也可以采用如下的方式，直接删除重复数据，如下语句将删除name相同的数据，其中id是主键。
DELETE t1 FROM table1 AS t1 JOIN table1 AS t2 ON t1.id>t2.id AND t1.name=t2.name;
5.4 分页算法
下面来看如下这个查询语句。
mysql> SELECT col_1,col_2 FROM profiles WHERE sex='M' ORDER BY rating limit 10;
如果没有索引，以上查询将会变得很慢，即使有了索引，也不一定会变快
。程序的展示页面可能是分页显示，如果有人点击的是中间的某个页面，类似如下的查询。
mysql> SELECT col_1,col_2 FROM profiles WHERE sex='M' ORDER BY rating limit 100000, 10;
这种查询，无论如何索引，效率都会奇差，因为大偏距（high offset）值的查询，会花费大部分时间来扫描大量数据，而这些数据最终都会被丢弃；
这种情况下，更好的办法是限制用户所看到的页，比如只提供最新的几页、上一页、下一页，因为没 有什么用户会去关注第10000页的内容。
或者换一个思路，用户点击1000页或10000页这个行为很稀少，那么根本没有必要做得很准确，自己根据数据库的数据估算总的页数，构建连接即可，有一些误差是可以接受的。
另一个办法是使用覆盖索引（covering index）。以下示例中的表已经在（sex，rating）上创建了索引，id是主键。
mysql> SELECT col_1,col_2 FROM profiles INNER JOIN (SELECT id FROM profiles WHERE x.sex='M' ORDER BY rating limit 100000, 10) AS x USING id;
以上语句中的SELECT子查询（SELECT id……）可以利用到覆盖索引，由于覆盖索引一般已被加载到内存，因此这种方式的排序效率会高许多。在一定的数据量下，性能尚可。
5.5 处理NULL值
对于SQL新手，NULL值的概念常常会造成混淆，他们常认为NULL与空字符串“''”是相同的，然而事实并非如此。
例如，下 述语句就是完全不同的。
mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');
这两条语句均会将值插入phone（电话）列，但第1条语句插入的是NULL值，第2条语句插入的是空字符串。
第1条语句的含义可被解释为“电话号码未知”，而第2条语句的含义可被解释为“该人员没有电话，因此没有电话号码”。
在SQL中，NULL值与任何其他值的比较（即使是NULL）永远都不会为“真”。
为了进行NULL处理，可使用IS NULL和IS NOTNULL操作符。如，
mysql> SELECT * FROM my_table WHERE phone IS NULL;
使用LOAD DATA INFILE读取数据时，对于空的或丢失的列，将用空字符串“''”来更新它们。
如果希望在列中具有NULL值，应在数据文件中使用\N。
使用DISTINCT、GROUP BY或ORDER BY时，所有NULL值将被视为是等同的。
使用ORDER BY时，首先将显示NULL值，如果指定了DESC按降序排列，那么NULL值将在最后面显示。
对于聚合（累计）函数，如COUNT（）、MIN（）和SUM（），将忽略NULL值。对此的例外是COUNT（*），它将计数行而不是单独的列值。
例如，下述语句会产生两个计数。首先计数表中的行数，其次计数age列中的非NULL值的数目：
mysql> SELECT COUNT (*), COUNT (age) FROM person;
对于某些列类型，MySQL将对NULL值进行特殊处理。
如果将NULL值插入TIMESTAMP列，那么将插入当前日期和时间。
如果将NULL值插入具有AUTO_INCREMENT属性的整数列，那么将插入序列中的下一个编号。
NULL值的存取，可能导致程序异常，我们有很多方法可用来友好地显示NULL值。
（1）使用CASE语句
SELECT CASE WHEN SUM(size) IS NULL THEN 0 ELSE SUM(size) END INTO @l_sum_vol FROM table_a ;
（2）使用COALESCE函数
SELECT COALESCE( sum(size) , 0 ) FROM table_a
COALESCE(value,...)函数：返回值为列表当中的第一个非NULL值，在没有非NULL值的情况下返回值将为NULL。
（3）使用IFNULL函数
SELECT SUM (ifnull(size,0)) FROM table_a;
IFNULL(expr1，expr2)函数：假如expr1不为NULL，则IFNULL（）的返回值为expr1；否则其的返回值为expr2。
IFNULL()的返回值是数字还是字符串取决于其所使用的语境。
（4）使用IF函数
SELECT SUM (IF (size is null, 0, size)) AS totalsize FROM table_a;
IF(expr1，expr2，expr3)：如果expr1是TRUE，则IF()的返回值为expr2；否则返回值为expr3。
IF()的返回值是数字还是字符串 视其所在的语境而定。
NULL值可能会导致MySQL的优化变得复杂，所以，一般建议字段应尽量避免使用NULL值。
5.6 存储URL地址
在互联网应用中，存储和检索域名或长的URL地址是很常见的。那么对于此类数据的存取，又有哪些技巧呢。
对于存储域名，可按照字符颠倒的方式进行存储，这样做可方便索引。
如：
com.fabulab.marcomacaco
com.fabulapps.kiko
com.fandora9.angryvirus
存储URL值，一般推荐的做法是对URL值做一个散列，散列值最好是整型，然后存储这个散列值，并在其上创建索引。
如下示例将对域名进行散列。 SELECT CONV (RIGHT(MD5(‘ http://www.mysql.com/’ ), 16), 16, 10) AS HASH64;
新建一个字段url_hash，用于保存类型为整型的散列值。
以后这样查询即可。 SELECT id FROM url WHERE url_hash=CONV(RIGHT(MD5('http://www.mysql.com/'), 16), 16, 10) AND url=http://www.mysql.com;
散列函数可以用程序来实现，以减少在MySQL侧的运算。
5.7 归档历史数据
随着项目的发展，将历史数据从日常使用的数据中删除，或将其移动到归档历史表中是比较常见的需求。
对过期数据的查询很少时，这样做可以提高性能，而且也不用对程序做大的变动。
还可以把过期的历史数据放到其他性能较差的实例、机器上，以便更好地利用资源。
另一种比较常见的方式，是按照时间分表，比如按月份、按日来分表存储数据。这种方式比较容易区分数据，方便维护。
但要留意如果有跨越多个表的查询，效率可能会比较差，需要综合考虑平衡分表的粒度。
笔者不建议使用MySQL自身的特性实现归档，比如分区表。
一般来说，把归档操作的逻辑放到程序处，可以更方便后期的 维护。
归档数据可以通过定时执行的守护执行，也可以使用一些特定的归档工具来归档数据。
还有，需要确保这种大数据量的操作不会影响到正常的生产。
5.8 使用数据库存储图片
如果使用文件系统或分布式文件系统存储图片，那么文件和数据库的信息可能难以保持一致，也不好回滚，不好统一进行备份，尤其是在多机房的环境下。
为了简化开发和架构，也可以考虑使用数据库来存储图片。
MySQL BLOB类型 （MEDIUM BLOB，最大支持16MB的数据）对于绝大部分图片来说都足够了，我们可以使用LOAD_FILE()方法读取一个文件， 然后将内容保存到BLOB列中。
由于数据库毕竟不适合于存储大量的图片，如果存储大量图片的话，仍然建议使用文件系统或分布式文件系统。
分布式文件系统配合缓存、CDN技术，往往是海量图片存储系统的优选方案。
5.9 多表UPDATE
MySQL可以基于多表查询更新数据。如下是MySQL多表UPDATE在实践中的几种不同写法。
对于多表的UPDATE操作需要慎重，建议在更新之前，先使用SELECT语句查询验证下要更新的数据与自己期望的是否一致。
假定我们有两张表，一张表为product表，存放产品信息，其中有产品价格列price；另外一张表是product_price表，要将 product_price表中的价格字段price更新为product表中价格字段price的80%。
在MySQL中我们有几种手段可以做到这一点，一种是“UPDATE table1 t1,table2,...,table n”的方式。
UPDATE product p, product_price pp SET pp.price = p.price * 0.8 WHERE p.productId = pp.productId
另外一种方法是使用INNER JOIN然后更新。
UPDATE product p INNER JOIN product_price pp ON p.productId = pp.productId SET pp.price = p.price * 0.8
也可以使用LEFT JOIN来做多表UPDATE，如果product_price表中没有产品价格记录的话，将product表的isDeleted字段设置为 1，SQL语句如下。
UPDATE product p LEFT JOIN product_price pp ON p.productId = pp.productId SET p.deleted = 1 WHERE pp.productId IS NULL
另外，上面的几个例子都是在两张表之间做关联，但是只更新一张表中的记录，其实MySQL是可以同时更新两张表的，如下查询就同时修改了两个表。
UPDATE product p INNER JOIN product_price pp ON p.productId = pp.productId SET pp.price = p.price * 0.8, p.dateUpdate = CURDATE()
两张表做关联，同时更新了product_price表的price字段和product表的dateUpdate两个字段。
5.10 生成全局唯一ID
由于分布式数据库的部署，多个节点之间为了避免数据冲突，需要有一个全局唯一的ID进行标识，
一些NoSQL数据库从设计之初，就考虑了ID的不重复，而MySQL在这方面仍然需要借助一些特殊的手段来生成全局唯一的ID。
可以考虑如下这些方式。
1）利用数据库自身的特性，在数据库启动参数里配置auto_increment_increment和auto_increment_offset，不过我们不推荐这种方式，因为这会导致数据库的维护成本上升。
2）配置一个单独的服务生成全局ID，可以是MySQL，也可以是NoSQL产品，甚至可以构建自己的专门用来生成唯一ID的服务，为了提高效率，还可以批量获取唯一的ID序列。
3）另外一种方式是，通过函数、程序算法或字段组合生成唯一ID，这种方式，可能会产生冲突，但是可以将这个冲突的概率做到非常小，我们更推荐使用这种方式。
5.11 使用SQL生成升级SQL
可以使用SQL去生成升级的SQL文件，如使用CONCAT函数拼接生成SQL语句。
例如，批量删除前缀为“prefix”的表，命令如下。
SELECT CONCAT ('drop table ',table_name,';') INTO OUTFILE '/tmp/drop_table.sql' FROM information_schema.tables WHERE table_name LIKE 'prefix%' AND table_schema='db_name';
小结:
我们使用一些技巧、方法，是为了更方便、更高效地使用数据库。
部分技巧的使用和具体数据库无关；部分技巧的使用，需要深入了解数据库。
在我们撰写代码的过程中，应该经常问自己，自己对于数据的操作，是否优雅、高效、 可扩展。
在这样的理念的引导下，我们将会变得越来越有技巧。
