讲义开发常用mysql v2

1、版本修改历史类别：A增加 M修改 D删除版本号日期类别描述编写审核V0.12013-07-20A新建文档V0.12013-07-23M修改文档V1.02013-07-26M修改文档V1.02013-07-28M修改文档V1.02013-08-05M修改文档目录V1.01开发实用技术4自增字段定义4字符串函数4CHAR(N)或 VARCHAR(N)中的 N 解释4日期操作函数5类型转换函数5开发规范6字段定义规范6绑定变量和替代变量使用规范7数据类型转换规范8SELECT * 的使用规范8字段上添加函数使用规范9表连接规范9分页查询规范101.2.变更管理10开发服务器说明10使用建议及命名规范

2、11使用建议115.5.1数据库涉及字符规范11数据库表命名规范12字段命名规范12外键命名规范12字段类型规范13索引使用原则13其他设计技巧131.开发实用技术自增字段定义字符串函数1.3 CHAR(N)或 VARCHAR(N)中的 N 解释1.4 日期操作函数1.5 类型转换函数1.1 自增字段定义类型为 或者 BIG自增字段类型必须是整型， 类型。并且自增字段必须是主键或1.2 字符串函数1.3 CHAR(N)或 VARCHAR(N)中的 N 解释 所有数据库的字符集都为 UTF8，字符集校对规则为 UTF8_general_ci。对于中文汉字，实际的时候占三个字节，而数据或字母，则只

3、占一个字节。例如： 中此两类字符串定义时候填写的长度N，不是字节数的意思 ，而是字符数的意思。中字符串连接方法，使用CONCAT() 或 CONCAT_ WS()函数，语法如下：CONCAT(string1,string2,.)CONCAT_ WS(separator,string1,string2,.)字符串长度统计：LENGTH(string)#返回 string 所占的字节数CHAR_LENGTH(string)#返回 string 中的字符个数统计字符个数，就不区分是汉字还是字母或数字，也跟字符集没有关系，若统计的是字节数，则由字符是汉字、字母或数字类型，以及字符集共同决定。请各位牢记

4、所有的数据库都将会采用 UTF8 编码，所以一个汉字占 3 个字节，一个字母或数字占一个字节。主键的一部分。1.4日期操作函数1.5类型转换函数字符串转换成日期方式，DATE_FORMAT()或 STR_TO_DATE()，两个函数的格式如下： DATE_FORMAT(expr1,format) STR_TO_DATE(expr1, format)常用的日期格式 YYYY-MM-DD HH:MM:SS 对应的 format 为%Y-%m-%d %H:%i:%S通用的类型转换函数： CAST(expr AS type) CONVERT(expr,type)CONVERT(expr USING t

5、ranscoding_name)获取当前时间：NOW()，CURDATE()、CURTIME()其中NOW() 函数精确到秒，格式：YYYY-MM-DD HH:MM:SSCURDATE() 函数精确到天，格式：YYYY-MM-DDCURTIME() 函数精确到秒，格式：HH:MM:SS日期数值的加减函数：DATE_ADD(date,ERVAL expr type) DATE_ SUB(date,ERVAL expr type)常用的几种 type 类型：YEAR、MONTH、DAY、HOUR、MINUTE，其中 expr 可以为正数或负数，在开过程中，一般使用DATE_ADD()函数，若要作日

6、期减去一个数字的方式，就使用负数。DATEDIFF(expr1,expr2)，是返回 开始日期expr1 与 结束日期expr2 之间，相差的天数 ，返回值为正数或负数。返回日期某部分信息的函数：YEAR(expr1) 返回日期expr1 部分的年份；MONTH(expr1) 返回日期expr1 部分的月份；DAY(expr1)返回 expr1 部分的天数；KDAY(expr1) 返回expr1 对应的数字CREATE TABEL gl_user(username VARCHAR(40);则 username 最多能40 个字符 。开发规范2.字段定义规范绑定变量和替代变量使用规范数据类型转换

7、规范SELECT * 的使用规范字段上添加函数使用规范表连接规范分页查询规范2.1 字段定义规范中用到的相关列数据类型需求与范围描述信息如下表列类型表达的范围需求TINY(M) UNSIGNED ZEROFILL-128 到 127 或 0 到 2551 个字节SMALL(M) UNSIGNED ZEROFILL-32768 到 32767 或 0 到 655352 个字节(M) UNSIGNED ZEROFILL-2147483648 到 2147483647 或 0 到4 个字节-到8 到157 或 08 个字节BIG(M) UNSIGNED ZEROFILLDECIMAL(M,D) UN

8、SIGNEDZEROFILL整数最大位数（ M ）为 65 ，小数位数最大（ D ）为 30变长DATEYYYY-MM-DD3 个字节DATETIMEYYYY-MM-DD HH:MM:SS(1001 年到 9999 年的范围 )8 个字节TIMESTYYYY-MM-DD HH:MM:SS （ 1970 年到 2037 年的范围）4 个字节0M=255（ 建议 CHAR(1) 外，超过此长度的用VARCHAR）M 个字符（所占空间跟字符集等有关系）CHAR(M)M 个字符（ N 大小由字符集，以及VARCHAR(M)0M65532/N是否为中文还是字母数字等 有关系）2.2 绑定变量和替代变量使

9、用规范基本原则：1. 所有 Query 的 Where 条件中的变量，都需要使用绑定变量来实现，此要求并不完全是基于性能的考虑，是基于安全方面的考虑，如若有任何不使用绑定变量的需求，都必须通过安全部门的审核并征得同意。详细说明：2. 在 iBatis 的 SqlMap 文件中绑定变量使用 #var_name#表示，替代变量使用$var_name$；所有需要动态 Order By 条件的 Query，在使用替代变量过程中，需要将可能传入的内容以枚举类写死在代码中接受任何外部传入内容；对于不变的常量条件，请使用常量而不是变量；3. IN 子句，使用Iterate + 数组类型变量的方式实现绑定变量

10、而不是通过代码拼接 Query语句，例如：#userIds#TEXT64K 个字符所占空间跟字符集等有关系详细说明：1. 所有动态长度字符串全部使用 VARCHAR 类型，类似于状态，有限类别的字段， 也使用可以比较明显表示出实际意义的字符串，而不应该使用之类的数字来代替；2. 固定长度的字符串使用 CHAR 类型，所有单个字符的全部使用 CHAR 类型，而不应该使用 VARCHAR 类型；3. 仅仅当字符数量可能超过 20000 个的时候，可以使用 TEXT 类型来存放字符类数据。所有使用 TEXT 类型的字段必须和原表进行分拆，与原表主键单独组成另外一个表进行存放；4. 需要精确到时间（年

11、月日时分秒）的字段可以使用 DATETIME 或 TIMEST，但请注意各自能表达的范围，以及是否需要用到 TIMEST的特性；5. 所有只需要精确到天的字段全部使用 DATE 类型，而不应该使用 TIMEST或者DATETIME 类型；6. 自增序列类型的字段只能使用或者 BIG，且明确标识出为无符号型(UNSIGNED)，除非确实会出现负数，仅当该字段数字取值会超过 42 亿，才使用 BIG类型；2.3数据类型转换规范2.4 SELECT * 的使用规范基本原则：在不必要查询中使用*列出所有字段，且需存在 GROUP BY 或 ORDER BY 的时候，使用 SELECT * 一次取出所有

12、的字段。对于表连接的 JOIN 语句，使用 SELECT * 来进行查询，除非明确获得 DBA 允许。含有 text 字段的表，当不需要取出 TEXT 字段的时候，也使用 SELECT * 进行查询.详细说明：进行 GROUP BY 或 ORDER BY 的时候不允许使用 SELECT * 是为了确保能够使用的优化排序算法.JOIN 语句不允许使用 SELECT * 是为了防止仅仅只需要索引即可完成的查询需要回表取数.存有 TEXT 字段表,在不需要取出 TEXT 字段的时候，不允许使用 SELECT * ,因为 TEXT 字段是存放在和普通不一样的物理位置，会造成大量的 io 操作.避免因增

13、删字段而没有修改相关SQL 及相关程序代码导致程序 BUG，而禁用 SELECT *.基本原则：在所有 Query 的 Where 条件中必须使用和过滤字段完全一致的数据类型，杜绝任何隐式类型转换，避免造成因为数据类型不匹配而导致 Query 执行计划的出错，造 能问题.详细说明：所有 Where 条件的字段上不允许使用函数做类型转换， 需要转换类型，只能转换过滤值,而不是转换字段.最为常见的隐式类型转换常见于时间类型与字符串类型之间，建议所有时间类型字段在 iBatis 中均以时间类型传入，或者以字符串传入然后通过时间函数转换字符串为合法的时间格式 ，如下：SELECT * FROM mem

14、ber WHERE gmt_create=DATE_FORMATE(2009010101:02:03,%Y-%m-%d %H:%i:%s);在表连接 Query 中，如果连接条件两端的数据类型不一致，必须保证将驱动表的连接条件数据类型转换为与被驱动表一致的数据类型.iBatis 会生成 t.user_id in (1,2,3,4,5 .)的语句2.5字段上添加函数使用规范2.6表连接规范过滤条件写到WHERE 字句中。出于代码的可读性原因，所有外连接 SQL 语句中，请一律使用LEFT JOIN，禁用 RIGHT JOIN。另外，请注意 LEFT JOIN 字句中，右边位置表的条件书写位置不同

15、的影响：SELECT A.rolename,A.gmt_create,B.nickname FROM gl_role A LEFT JOIN gl_roledetail B ON A.ID=B.roleid AND B.roleID=2;+-+-+| rolename| gmt_create| nickname |+-+-+| 0000-00-00 00:00:00 | test2| 0000-00-00 00:00:00 | NULL| 0000-00-00 00:00:00 | NULL| 2009-08-20 18:20:18 | NULL|+-+-+SELECT A.rolename,

16、A.gmt_create,B.nickname FROM gl_role A LEFT JOIN gl_roledetail B ON A.ID=B.roleid WHERE B.roleID=2;+-+-+| rolename | gmt_create| nickname |基本原则：所有非外连接 SQL（即 INNER JOIN），请把关联表写到 FROM 字句中，关联条件与基本原则：在 WHERE 条件中出现的过滤字段上，使用任何函数进行类型或格式的转换；正确的做法是把传入比较的值转换为列类型所需要的。错误的写法：SELECT username FROM gl_user WHERE DA

17、TE_FORMAT(gmt_create,%Y%m%d%H%i%s)=20090501022300;正确的写法：SELECT username FROM gl_user WHERE gmt_create=DATE_FORMAT(20090501022300, %Y-%m-%d %H:%i:s);5) WHERE 条件查询，如果有条件中有日期类型的字段，使用大于”和小于”符号，使用 betn 和 and。2.7 分页查询规范基本原则：分页查询语句全部都需要带有排序条件，除非商业方明确要求不要使用任何排序来随机展示数据。详细说明：1) 常规分页语句写法(start：起始数，page_offset：

18、每页数)：SELECT ID,username FROM gl_user WHERE username like ORDERBY M.gmt_creaIMIT start, page_offset;2) 多表 Join 的分页语句，如果过滤条件在单个表上，需要先分页，再 Join：低性能写法：SELECT M.username,P.rolename FROM gl_user M INNER JOIN gl_role P ONM.ID=P.userid WHERE username like LIMIT start, page_offset;高性能写法： ORDER BYM.gmt_create

19、SELECT M.username,P.rolename FROM (SELECT ID,usernameFROM gl_userIMIT start,WHERE username like page_offset) M,gl_role这样写的前提是关联的表之间page_offset 的值。 ORDER BY M.gmt_creaP WHERE M.ID=P.userid;一一对应，否则可能会返回的数目少于或多于3.变更管理3.1 有非小表（不超过 10W 条）结构变更的发布至少提前 1通知发布具体时间，超过 10w 条的结构变更必须提前 2通知3.2 每天的 09:00 12:00 与 14

20、:00 16:00 之间一般不对产品数据库做任何变更（备注：数据库结构变更）操作+-+-+| 0000-00-00 00:00:00 | test2|+-+-+4.开发服务器说明Server = MYDatabase = db_992f63_mydb Uid = 992f63_mydbPwd = mydb_123使用建议及命名规范5.5.1使用建议5.2数据库涉及字符规范采用26 个英文字母(区分大小写)和 0-9 这十个自然数,加上下划线_组成,共 63 个字符.不能出现其他字符(注释除外).注意事项:1) 进行数据库结构设计的时候，考虑适当的冗余，尽量确保应用读写数据的 SQL 简洁.2)

21、 所有字符集为utf8，校对规则为 utf8_general_ci ，默认是不区分英文字母大小写，若有需求区分大小写，请跟 DBA 特别，或者表定义语句指定 COLLATE utf8_bin.3) 尽量不需要使用子查询，特别是 IN 的方式，可考虑转化为 EXISTSSELECT * FROM A WHRE A.ColName1 IN (SELECT DISTINCT ID FROM B WHERE .);建议改写为：SELECT * FROM A WHRE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ColName1.);要返回自增序列的 ID 值，可以考虑使用

22、函数 LAST_INSERT_ID()，此函数只能返回同一个 SES最近一次对有 AUTO_INCREMENT 属性表 INSERT 的 ID 值.所有的时间字，请以数据库的时钟为准，除用户输入的时间值外.对于项目的数据量、PV 等合理评估，DBA TEAM 相关，会给大家合理成数据存取架构，增强系统的扩展性与用户体验，以及高可用性等.开发服务器相关信息：5.3数据库表命名规范5.4字段命名规范5.5外键命名规范字段名称全部使用大写；.不需要加表名的前缀；如果需要有多个单词组合的，用下划线”_” 连接；如 用户名：USER_NAME用户 ID：USER_ID表与表之间的相关联字段要用名称,如

23、info_user 表里面的 userId 和 group 表里面的 userId 相对应；业务流水号采用：表名_seq；数据表名全部使用小写英文以及下划线组成比如:info_usersystem_destination信息类采用：info_文件类采用：file_关联类采用： er_备份数据表名使用正式表名加上备份时间组成,如: info_user_20130720 system_destination_20130720以上命名都不得超过 30 个字符的系统限制.变量名的长度限制为 29(不包括标识字符 ).数据对象、 变量名都采用英文字符,使用中文命名.绝对不要在对象名的字符之间留空格.保留词,要保证你的字段名没有和保留词、数据库系统或者常用方法5) 保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性.假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了.5.6 字段类型规范5.7 索引使用原则逻辑主键使用唯一的成组索引,对系统键(作为过程)采用唯一的非成组索引,对任何外键列采用非成组索引.考虑数据库的