Mysql 建表及查询规范

1.字段对象设计规范

      INT：如无特殊需要，存放整型数字使用UNSIGNED INT型。整型字段后的数字代表显示长度。整型类型如下表：

数据类型	最大存储长度（有符号）	最大存储长度（无符号）
tinyint(m)	1个字节 范围(-128~127)	1个字节 范围(0~256)
smallint(m)	2个字节 范围(-32768~32767)	2个字节 范围(0~65535)
mediumint(m)	3个字节 范围(-8388608~8388607)	3个字节 范围(0~16777215)
int(m)	4个字节 范围(-2147483648~2147483647)	4个字节 范围(0~4294967294)
bigint(m)	8个字节 范围(+-9.22*10的18次方)	8个字节 范围(0~1.84*10的20次方)

    DECIMAL(M,D)：定点小数使用此DECIMAL类型,且明确标识出为无符号型(UNSIGNED),除非确实会出现负数。 

    DATE：所有只需要精确到天的字段全部使用DATE类型,而不应该使用TIMESTAMP或者DATETIME类型。

    DATETIME：所有需要精确到时间(时分秒)的字段均使用DATETIME,不要使用TIMESTAMP类型。

    VARCHAR：所有动态长度字符串 全部使用VARCHAR类型。如无特殊需要，原则上单个varchar型字段不允许超过255个字符。

    CHAR:仅仅只有单个字符的字段使用CHAR(1)类型，例如性别字段。如无特殊需要，建议INNODB引擎不使用CHAR型 。

    TEXT:仅仅当字符数量较多时,才允许使用TEXT。如无特殊需要，严禁开发人员使用MEDIUMTEXT、LONGTEXT类型。

    对于精确浮点型数据存储，需要使用DECIMAL，严禁使用FLOAT和DOUBLE。 

    如无特殊需要，严禁开发人员使用BLOB类型。 

    如无特殊需要，字段必须使用NOT NULL属性，可用默认值代替NULL。MySQL NULL类型和Oracle的NULL有差异，会进入索引中。此外，NULL在索引中的处理也是特殊的，也会占用额外的存放空间。

    不建议使用ENUM、SET类型，使用TINYINT来代替。 

    每个列定义的时候必须加上comments。

    自增字段类型必须是整型且必须为UNSIGNED，推荐类型为INT或BIGINT，并且自增字段必须是主键或者主键的一部分。

    日期类型的字段不能使用VARCHAR或者CHAR类型，只能使用DATE、DATETIME字段类型存放。

2.sql语句开发规范

    sql语句尽可能简单，大的sql想办法拆成小的sql语句(充分利用QUERY CACHE和充分利用多核CPU)。

    事务要简单，整个事务的时间长度不要太长。

    避免使用触发器、函数。

    禁止使用存储过程。

    降低业务耦合度，为sacle out、sharding留有余地。

    避免在数据库中进行数学运算(MySQL不擅长数学运算和逻辑判断)。

    不要用select *，查询哪几个字段就select 这几个字段。

     sql中使用到OR的改写为用 IN()   (or的效率没有in的效率高)，in里面数字的个数建议控制在1000以内。

     limit分页注意效率。Limit越大，效率越低。可以改写limit，比如例子改写：select id from tlimit 10000, 10;  =>  select id from t where id > 10000 limit10。

     避免使用大表的JOIN，使用group by 分组、自动排序。

     对数据的更新要打散后批量更新，不要一次更新太多数据，减少与数据库的交互次数。

     SQL语句不可以出现隐式转换，比如 select id from 表 where id='1'。

     能不用NOT IN就不用NOT IN，坑太多了。。会把空和NULL给查出来。

     在SQL语句中，禁止使用前缀是%的like。

     不使用负向查询，如not in/like。

     关于分页查询：程序里建议合理使用分页来提高效率limit，offset较大要配合子查询使用。

     禁止在数据库中跑大查询。

     使用预编译语句，只传参数，比传递SQL语句更高效；一次解析，多次使用；降低SQL注入概率。

     禁止使用order by rand()。

     禁止单条SQL语句同时更新多个表。

备注:公司前辈经验之谈,受益匪浅!