分布式事务数据库 —-MySQL 数据库开发规范

Amygo

（4）函数

4.1 字符串连接函数
MySQL 数据库中字符串连接方法，需使用 CONCAT() 或 CONCAT_ WS()函数，语法如下：
CONCAT(string1,string2,…)
CONCAT_ WS(separator,string1,string2,…)
MySQL 中的 CONCAT 和 Oracle 中的 CONCAT 不同，可以接受任意多个参数，可以较为简
单的替代’||'符号的作用。MySQL 也可以通过 sql_mode 配置双管道符作为字符串连接运算符，
但不推荐这样做。

4.2 字符串长度统计函数
LENGTH(string) #返回 string 字符串所占的字节数
CHAR_LENGTH(string) #返回 string 字符串中的字符个数
统计字符个数，就不区分是汉字还是字母或数字，也跟字符集没有关系，若统计的是字
节数，则由字符是汉字、字母或数字类型，以及字符集共同决定。

4.2.1 特别说明
我们所有的 MySQL 数据库都将会采用统一的 UTF8 编码，所以一个汉字占 3 个字节，
中文输入法（或称全角输入模式）下的字母或数字占 3 个字节；英文输入法（或半角输入法
模式）下一个字母或数字占 1 个字节。

4.3 字符串判断函数
IF(exp1,exp2,exp3)：若是 exp1 为真，返回 exp2；若是 exp1 为假，返回 exp3；
IFNULL(exp1,exp2)：若是 exp1 IS NOT NULL，返回 exp1，否则返回 exp2；
NULLIF(exp1,exp2)：若是 exp1=exp2，返回 NULL，否则返回 exp1；

4.4 字符串替换函数
LTRIM(exp1)：去掉 exp1 中字符串开头的空格；
RTRIM(exp1)：去掉 exp1 中字符串结尾的空格；
TRIM(exp1)：去掉 exp1 中的开头和结尾的空格；
TRIM(exp2,exp1)：去除掉 exp1 中存在的字符串 exp2；

Amygo

4.5 字符串查找函数
SUBSTRING_INDEX(exp1,delim,count)
exp1 为 字 符 串 ， delim 为 分 割 符 号 ， count 表 示 第 几 个 风 格 符 号 ， 例 如 ：
SUBSTRING_INDEX，返回：ali
SUBSTRING(exp1,pos,len)
exp1 为字符串，pos 为位置，len 为长度，例如：SUBSTRING，返回：ali。
LOCAL(substr,str)
查找 substr 在 str 中的第一个位置，例如：LCOAL，返回：6。

4.6 字母大小写转换函数
UPPER(exp1)：把字符串 exp1 转换成大写；
LOWER(exp1)：把字符串 exp1 转换成小写；

4.7 数学函数
ABS(value)：返回 value 的绝对值，例：ABS(-101)，返回：101
FLOOR(value)：去掉 value 的小数，例：FLOOR(2013.8)，返回：2013
MOD(N,M)：返回 N 除以 M 的余数，例：MOD(2013,10)，返回：3
ROUND(value)：返回 value 的四舍五入值，例：FLOOR(2013.8)，返回：2014
ROUND(value,num)：保留 num 位小数 value 的四舍五入值，例FLOOR(2013.867,1)，返回：2013.9
RAND()：返回随机数值

Amygo

4.8 日期操作函数
获取当前时间函数：NOW()、CURDATE()、CURTIME()
NOW()函数精确到秒， 格式：YYYY-MM-DD HH:MM:SS
CURDATE()函数精确到天， 格式：YYYY-MM-DD
CURTIME()函数提供小时、分钟、秒， 格式：HH:MM:SS

4.8.1 特别说明
从主备数据异步复制的数据安全性角度出发，禁止使用 SYSDATE()函数获取当前时间。
日期数值的加减函数
DATE_ADD(date,INTERVAL expr type)
DATE_ SUB(date,INTERVAL expr type)
常用的几种 type 类型：YEAR、MONTH、DAY、HOUR、MINUTE，其中 expr 可以为正数或负数，我们在开过程中，一般使用 DATE_ADD()函数，若要做日期减去一个数字的
方式，就使用负数。
MySQL 中不能像 Oracle 那样直接对时间类型进行加减运算，直接使用加减运算符不会得到符合预期的结果，需要使用函数进行运算。
DATEDIFF(expr1,expr2)，是返回 开始日期 expr1 与 结束日期 expr2 之间，相差的天数 ，返回值为正数或负数。
返回日期某部分信息的函数
YEAR(expr1) 返回日期 expr1 部分的年份；
MONTH(expr1) 返回日期 expr1 部分的月份；
DAY(expr1)返回 expr1 部分的天数；
WEEKDAY(expr1)返回 expr1 对应的星期数字，0 表示星期一，1 表示星期二，其他依次类推；

Amygo

4.9 类型转换函数
Ø 日期类型格式转换
字符串转换成日期方式，DATE_FORMAT()或 STR_TO_DATE()，两个函数的格式如下：
DATE_FORMAT(expr1,format)
STR_TO_DATE(expr1, format)

4.9.1 特别说明
STR_TO_DATE()是为兼容 Oracle 数据库对应函数。
常用的日期格式 YYYY-MM-DD HH:MM:SS 对应的 FORMAT 为：%Y-%m-%d %H:%i:%S。
通用类型转换函数
CAST(expr AS type) 则是把 expr 数字或字符串 转换为 type 类型；
CONVERT(expr,type) 则是把 expr 数字或字符串 转换为 type 类型；
CONVERT(expr USING transcoding_name) 则是转换字符串或字段的字符集编码；
日期整型转换函数
UNIX_TIMESTAMP(date_string) 则是将字符串格式表达的日期转换成 INT 无符号类型的整
型数值，例如：

Amygo

FROM_UNIXTIME(int_value) 则是将用整型数值表达的日期转换成字符串格式的日期，例
如：

Amygo

IP 地址转换
可以使用 INET_ATON()、INET_NTOA()、INET6_ATON()、INET6_NTOA()实现 IP 地址和
整型值之间的转换。
隐式转换
在不同类型的列之间发生比较或运算时，MySQL 遵循如下规则：
NULL 和 NULL 比较不转换；
字符串和不同的字符串比较，则使用字符串比较；
整型和不同的整型比较，则使用整型比较；
十六进制值和整型以外的类型比较时十六进制值视为二进制字符串；
时间类型和字符串常量比较时，字符串转换为时间类型进行比较；
DECIMAL 类型的比较方式取决于另一个值的类型，如果是整型，则使用 DECIMAL 比
较；如果是浮点型，则使用浮点类型比较。其它情况，使用浮点比较；

Amygo

4.10 特殊函数
SYSDATE()
原意为获取调用函数时刻的时间，通常在标准化的 MySQL 配置中，会将其修改为 NOW()
的同义词，为 SQL 语句开始执行的时间。从而规避 SYSDATE 带来的复制问题与
sysdate-is-now 参数带来的行为改变导致的潜在的配置不一致风险两个角度考虑，故建议禁
止使用函数 SYSDATE()。
LAST_INSERT_ID()
获取最近成功插入带自增长的表的数据行的自增长 ID 值，在分布式数据库开发当中，因可
能涉及到分布式事务，以及全局自增 ID，因此，禁止使用该函数。
SLEEP()、BENCHMARK()
应用程序中要禁止使用此类函数，及防 SQL 注入方式对待。
UUID()
生产全局唯一 ID 的函数，MySQL 使用通用的算法，算法版本为 1，该函数不能确保绝对不
发生冲突，但是冲突的概率极低。由于 UUID 分布的随机性，非常不适合作为 InnoDB 存储
引擎表的主键或唯一性的标识，非特殊原因或特殊场景不应使用。
GET_LOCK()、RELEASE_LOCK()、IS_FREE_LOCK()、IS_USED_LOCK()
用户锁函数，对于定时调度存储过程，该系列函数可以用于确保只有一个存储过程正在运行
类型的特殊功能，或者控制应用程序单线程运行类型的特殊功能。我们建议不要在分布式数
据库中使用存储过程，因此，也禁止使用此类函数。

4.11 聚合函数
分布式数据库 能够支持聚合函数与 GROUP BY，ORDER BY，HAVING 的使用；也
可以在 JOIN 语句中使用聚合函数。如：
SELECT count(t.id) FROM t LEFT JOIN t1 on t.id=t1.id GROUP BY t.name;

4.11.1 嵌套聚合函数
禁止在分布式数据库中使用 MAX(AVG())、COUNT(DISTINCT)这之类的嵌套聚合函数。

Amygo

我又来更新了

5.1 简化查询语句**
5.1.1 去掉不必要的子查询
SELECT * FROM （SELECT column1,column2 FROM table_name WHERE column1=xx） limit 10;
修改成
SELECT column1,column2 FROM table_name WHERE column1=xx LIMIT 10;
5.1.2 去掉不必要的括号
((a AND b) AND c OR (((a AND b) AND (c AND d))))
修改成
(a AND b AND c) OR (a AND b AND c AND d)
示例：

5.1.3 去掉重叠常量
示例：
(a<b AND b=c) AND a=100
修改成 b>100 AND b=c AND a=100
5.1.4 因常量重叠而去除部分常量条件
示例：
(B>=100 AND B=100) OR (B=200 AND 100=100) OR (B=300 AND 100=200)
修改成 B=100 OR B=200
5.1.5 去掉无意义的筛选条件
示例：
1=1，2>1，1<2 等
修改成 这些无意义的筛选条件可以直接从 WHERE 子句中去掉。
5.1.6 多使用等值操作，少使用非等值操作
WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不
了索引，因为不能同时用到两个范围条件。
---------------------
作者：Amy—go

Amygo

《分布式事务数据库 —-MySQL 数据库开发规范》一文更新到现在已经过半了~Amy对之前的目录进行了调整——也是为了让大家能够更早的看到这篇诚意十足的干货！今天为大家更新第六节“SELECT 子句的使用规范”。感兴趣的可以复习一下之前的五节。
当然,如果我的分享有帮助到你，可以继续关注我哦~有什么疑惑或者不足之处也欢迎大家在评论里指出！

SELECT 子句的使用规范
6.1 基本原则
在不必要查询中使用“*”列出所有字段，且存在 GROUP BY 或 ORDER BY 的时候，禁止使用 。SELECT * 一次取出所有的字段。对于表连接的 JOIN 语句，禁止使用 SELECT * 来进行查询。含有 text 字段的表，当不需要取出 TEXT 字段的时候，也禁止使用 SELECT * 进行查询。
6.2 详细说明
1> 进行 GROUP BY 或 ORDER BY 的时候不允许使用 SELECT * 是为了确保 MySQL 能够使用最新的优化排序算法；
2> JOIN 语句不允许使用 SELECT * 是为了防止仅仅只需要索引即可完成的查询需要回表取数；
3> 存有 TEXT 字段表,在不需要取出 TEXT 字段的时候，不允许使用 SELECT * ,因为 TEXT字段是存放在和普通记录不一样的物理位置，会造成大量的 IO 操作；
4> SELECT * 会增加 CPU、IO 开销和占用更多的网络带宽，也影响性能；
5> 避免因增删字段而没有修改相关SQL及相关程序代码导致程序BUG，而禁用SELECT *；
6> SELECT 子句部分不要出现前台不需要或后续处理不需要的字段，尤其不要出现TEXT/BLOB 等不需要的大字段。
---------------------
作者：Amy—go

Amygo

今天Amy继续为大家更新**“WHERE条件子句"**的内容~相信学完这章大家应该对分布式事务数据库的开发有了深入的认识。接下来我会为大家奉上更多实用的开源数据库和MySQL常用工具的干货分享。感兴趣的读者可以继续关注我哦。

WHERE 条件子句
7.1 WHERE 子句中的数据扫描不超过表总数据量的 30%
比如：WHERE primary_key <> 1 或者 primary_key not in(…)，这样扫描表的数据往往会超过
30%。
WHERE status=1，其中 1 值非常少，主要是 0 值，比如一个表的记录删除用了一个状态位，
而删除的记录又比较少。
WHERE 子句中同一个表的不同的字段组合建议小于等于 5 组，否则建议业务逻辑拆分
或分表。
WHERE 子句禁止出现 NULL 值计算
WHERE 子句中不允许出现更不允许出现对 NULL 值的错误计算方式：Column1=NULL 和
(column1>NULL 或 Column1<NULL)。故应该在字段定义时设置约束为 NOT NULL。
7.2 WHERE 子句的表字段上禁止使用表达式或是函数
当 WHERE 子句的表字段上使用函数后，将会导致该字段无法使用上索引，一般都是建议
在字段的值域上加函数、计算或转换的表达式。例如：