mysql创建表的规范

名称

1. 建表的时候，给表，字段和索引起个好名字
   1. 见名知意：好的名字能够降低沟通和维护的成本
   2. 名字不宜过长，尽量控制在30个字符以内
2. 大小写
   1. 名字尽量都用小写字母，因为从视觉上，小写字母更容易让人读懂
   2. 全部大写，看起来不太直观，一部分大写一部分小写更不可以
3. 分隔符
   1. 单词之间没有分隔，或者单词间用驼峰标识，或者单词间用空格分隔，或者单词间用@分隔，都不建议
   2. 建议在单词之间使用下横线_分隔
4. 表名
   1. 对于表名，在言简意赅，见名知意的基础上，建议带上业务前缀
   2. 如果是订单相关的业务表，可以在表名前面加个前缀：order_，比如order_pay
   3. 这样做的好处是为了方便归类，把相同业务的表，可以非常快速的聚集在一起
   4. 如果哪天有非订单的业务，比如：金融业务，也需要建一个名字叫做pay的表，可以取名：finance_pay，就能非常轻松的区分，这样就不会出现同名表的情况
5. 字段名称
   1. 比如有些表用flag表示状态，而有些表使用status表示状态，可以统一一下，使用status表示状态
   2. 如果一个表使用了另一个表的主键，可以在另一张表的名后面，加_id,例如：product_spu_id
   3. 创建时间，可以统一为：create_time，修改时间统一为：update_time
   4. 删除状态固定位：delete_status
   5. 还有很多公共字段，在不同表之间，可以使用全局统一的命名规则，定义成相同的名称，以便大家好理解
6. 索引名
   1. 普通索引和联合索引，其实是一类，在建立该类索引时，可以加ix_前缀，比如：ix_product_status
   2. 唯一索引，可以加ux_前缀，比如：ux_product_code

字段类型

1. 时间格式的数据有：date,datetime,timestamp等可以选择
2. 字符类型的数据有：varchar,char,text等可以选择
3. 数字类型数据有：int,bigint,smallint,tinyint等可以选择
4. 如果字段类型选大了，比如原本只有1-10之间的10个数字，结果选了bigint，他占了8个字节，其实1-10之间的10个数字，每个数字1个字节就能保存，选择tinyint更为合适，这样会白白浪费7个字节的空间
5. 如果字段类型选小了，比如：一个18位的id字段，选择了int类型，最终数据会保存失败
6. 所以选择一个合适的字段类型，是很重要的
7. 参考原则：
   1. 尽可能选择占用存储空间小的字段类型，在满足正常业务需求的情况下，从小到大，往上选
   2. 如果字符串长度固定，或者差别不大，可以选择char类型，如果字符串长度差别较大，可以选择varchar类型
   3. 是否字段，可以选择bit类型
   4. 枚举字段：可以选择tinyint字段
   5. 主键字段：可以选择bigint类型
   6. 金额字段：可以选择decimal类型
   7. 时间字段：可以选择timestamp或datetime类型

字段长度

1. 在mysql中除了varchar和char是代表字符长度外，其余类型都是代表字节长度
2. bigint(4)，bigint实际长度为8个字节，现在有一个数据a=1.a显示4个字节，所以在不满足4个字节时前面填充0(前提是该字段设置了zerofill属性)，比如：0001，当满了4个字节时，比如现在数据是a=123456，他会按照实际的长度显示，比如123456，但需要注意的是，有些mysql客户端即使满了4个字节，也只能显示4个字节的内容，比如会显示成：1234，所以bigint(4)，这里的4表示显示的长度为4个字节，实际长度还是占8个字节

字段个数

1. 建表时需要对字段的个数做一定的限制
2. 如果表的字段个数非常多，可以将一张大表拆分成多张小表，这几张表的主键相同
3. 建议每表的字段个数，不超过20个

主键

1. 在创建表时，一定要创建主键，因为主键自带了主键索引，相比于其他索引，主键索引的查询效率更高，因为他不需要回表
2. 主键还是天然的唯一索引，可以根据他来判重
3. 在单个数据库中，主键可以通过auto_increment，设置为自动增长的
4. 但在分布式数据库中，特别是做了分库分表的业务库中，主键最好由外部算法(比如：雪花算法)生成，他能够保证生成的id是全局唯一的
5. 主键建议保存跟业务无关的值，减少业务耦合性，方便今后的扩展
6. 不过也有一些一对一的表关系，比如：用户表和用户扩展表，在保存数据时是一对一的关系，这样用户扩展表的主键，可以直接保存用户表的主键

存储引擎

1. 在mysql8之前的版本，默认的存储引擎是myisam，而mysql8以后的版本，默认的存储引擎是innodb
2. myisam的索引和数据分开存储，有利用查询，但他不支持事务和外键等功能
3. innodb虽然查询性能稍微弱一点，但他支持事务和外键等，功能更强大一些
4. 以前的建议是：读多写少的表，用myisam存储引擎，而写多读多的表，用innodb
5. 但随着mysql对innodb存储引擎性能的不断优化，现在myisam和innodb查询性能相差已经越来越小
6. 所以在使用mysql8之后的版本时，直接使用默认的innodb存储引擎即可，无需额外修改存储引擎

not null

1. 定义字段时，应该尽可能明确字段为NOT NULL
   1. 在innodb中，需要额外的空间存储null值，需要占用更多的空间
   2. null值可能会导致索引失效
   3. null值只能用is null或者is not null判断，用=号判断永远返回false
2. 因此，建议定义字段时，能定义为not null，就定义为not null
3. 如果某个字段直接定义成not null，万一有些地方忘了给该字段写值，就会insert不了数据
4. alter table product_sku add column brand_id int(10) not null default 0;

外键

1. 在mysql中，是存在外键的
2. 外键存在的主要作用是：保证数据的一致性和完整性
3. foreign key(cid) references class(id),student表的cid字段，保存的class表的id，这时通过foreign key增加了一个外键
4. 如果直接通过student表的id删除数据，会报异常：a foreign key constraint fails
5. 必须先删除class表对应的cid那条数据，再删除student表的数据才行，这样能够保证数据的一致性和完整性
6. 只有存储引擎是innodb时，才能使用外键
7. 一般不建议使用外键，因为这类系统更多的是为了性能考虑，宁可牺牲一点数据一致性和完整性
8. 除了外键之外，存储过程和触发器也不建议使用，会影响性能

索引

1. 在建表时，除了指定主键索引外，还需要创建一些普通索引
2. id int(10) primary key auto_increment
3. 普通索引：key ‘ix_spu_id’ (‘spu_id’) using btree
4. 后面查询表的时候，效率更高
5. 但索引字段也不能建的太多，可能会影响保存数据的效率，因为索引需要额外的存储空间
6. 建议单表的索引个数不要超过5个
7. 如果在建表时，发现索引个数超过5个了，可以删除部分普通索引，改成联合索引
8. 在创建联合索引时，需要注意最左匹配原则，不然，建的联合索引效率可能不高
9. 对于数据重复率非常高的字段，比如：状态，不建议单独创建普通索引，因为即使加了索引，如果mysql发现全表扫描效率更高，可能会导致索引失效

时间字段

1. 时间字段的类型，目前mysql支持：date，datetime，timestamp，varchar等
2. varchar类型可能是为了跟接口保持一致，接口中的时间类型为String
3. 但是如果需要通过时间范围查询数据，效率会非常低，因为这种情况没法走索引
4. date类型主要为了保存日期，比如：2024-07-02，不适合保存日期和时间，比如：2024-07-02 21:05:30
5. 而datetime和timestamp类型更适合保存日期和时间
6. timestamp：用4个字节来保存数据，他的取值范围为1970-01-01 00:00:00UTC - 2038-01-19 03:14:07,此外，还跟时区有关
7. datetime：用8个字节保存数据，他的取值范围为：1000-01-01 00:00:00 - 9999-12-31 23:59:59，他和时区无关
8. 优先推荐使用datetime类型保存时间和日期，可以保存的时间范围更大
9. 在给时间字段设置默认值时，建议不要设置成：0000-00-00 00:00:00 不然查询表时可能会转换不了直接报错

金额字段

1. mysql中有多个字段可以表示浮点数：float，double，decimal等
2. float和double可能会丢失精度，因此推荐使用decimal类型保存金额
3. 一般这样定义浮点数：decimal(m,n)
4. 其中的n是指小数的长度，而m是指整数加小数的总长度
5. 比如：decimal(10,2),则表示整数长度为8，并且保留2位小数

JSON字段

1. 某个字段保存的数据值不固定
2. mysql支持按字段，查询json中的数据

唯一索引

1. 可以给单个字段，加唯一索引
2. 也可以给多个字段，加一个联合的唯一索引，联合的唯一索引，字段值出现null时，则唯一性约束可能会失效
3. 创建唯一索引时，相关字段一定不能包含null值，否则唯一性会失效

字符集

1. mysql支持的字符集有很多：latin1，utf-8，utf8mb4，GBK
2. GBK：长度2，支持中文，但是不是国际通用的字符集
3. UTF-8：长度3位，支持中英文混合场景，是国际通用字符集
4. latin1：长度1位，mysql默认的字符集
5. utf8mb4：长度4位，完全兼容UTF-8，用四个字节存储更多的字符
6. latin1容易出现乱码问题，子啊实际项目中使用比较少
7. 而GBK支持中文，但不支持国际通用字符，在实际项目中使用也不多
8. 目前，mysql的字符集使用最多的还是utf-8和utf8mb4
9. 其中utf-8占用3个字节，比utf8mb4的4个字节，占用更小的存储空间
10. 但UTF-8有个问题：无法存储emoji表情，因为emoji表情需要4个字节，保存时会直接报错
11. 建议在建表时字符集设置成：utf8mb4，会省去很多不必要的麻烦

排序规则

1. 在mysql中创建表时，有个collate参数可以设置排序规则
2. collate=utf8mb4_bin
3. 字符排序规则跟字符集有关，比如字符集如果是utf8mb4，则字符排序规则也是以utf8mb4_开头的，常用的有：utf8mb4_general_ci,utf8mb4_bin等
4. 其中utf8mb4_general_ci排序规则，对字母的大小写不敏感，不区分大小写
5. 而utf8mb4_bin排序规则，对字符大小写敏感，区分大小写
6. 比如：order表中有一条记录，name的值是大写的YOYO，但我们用小写的yoyo去查，select * from order where name = ‘yoyo’;
7. 如果字符排序规则是utf8mb4_general_ci，则可以查出大写的YOYO的数据
8. 如果字符排序规则是utf8mb4_bin，则查不出来
9. 因此，字符排序规则，要根据实际的业务场景选择，否则容易出现问题

大字段

1. 大字段，即占用较多存储空间的字段，比如用户评论
2. 如果直接定义为text类型，可能会浪费存储空间，所以建议讲这类字段定义为varchar类型的存储效率更高
3. 如果是合同数据，一个合同可能会占几MB，可以保存到mongodb中，然后在mysql的业务表中，保存mongodb表的id

冗余字段

1. 在设计表的时候，为了性能考虑，提升查询速度，有时可以冗余一些字段
2. 对查询性能有利，但需要额外的存储空间，还可能会有数据不一致的情况，比如用户名称修改了
3. 我们在实际业务场景中，需要总和评估，冗余字段方案不适用于所有的业务场景

注释

1. 在做表的设计时，一定要把表和相关字段的注释加好，并且经常需要更新这些注释

2. ‘valid_status’ tinyint(1) not null default 1 comment ‘有效状态 1：有效 0 ：无效’

3. 特别是有些状态类型的字段，比如valid_status字段，该字段表示有效状态，1：有效，0：无效，

4. 让人可以一目了然，表和字段是干什么用的，字段的值可能有哪些