避免踩坑！数据库建表的18个最佳实践- 前言
文章目录
避免踩坑！数据库建表的18个最佳实践- 前言思维导图基础信息名字字段类型字段长度字段个数
约束主键NOT NULL外键唯一索引
性能优化存储引擎索引排序规则
特殊字段时间字段金额字段json字段大字段
其他字符集冗余字段注释
嗨！ 后端开发的小伙伴们
今天，我想和大家分享一些关于数据库建表的 小技巧 ，帮助你避免踩坑，提高效率！
为什么要学习数据库建表
数据库是系统中存储核心数据的关键组件 。后端开发人员经常需要创建和维护数据库表，以满足业务需求 。如果建表时不注意细节，后期维护成本会很高，甚至会踩坑 。
这篇文章将介绍 18 个数据库建表的最佳实践，帮助你
选择合适的数据类型 ️设计字段和索引设置表约束 ⛔优化表性能
这些技巧都是我在工作中总结出来的，非常有借鉴意义，希望对你有所帮助
✨ 文章亮点：
满满的干货，都是实践经验避免踩坑，少走弯路提升效率，快速开发通俗易懂，小白也能看
继续阅读，了解数据库建表的18个小技巧
思维导图
基础信息
名字
数据库表、字段和索引命名规范
见名知意 名字要言简意赅，见名知意，让人一看就知道是什么意思。 避免使用模糊、不明确的命名。 名字不宜过长，尽量控制在30个字符以内。
大小写 名字尽量都用小写字母，因为从视觉上，小写字母更容易让人读懂。 避免使用大写字母或混合大小写。
分隔符 多个单词间，建议用下划线_分隔。 避免使用空格、驼峰标识或其他特殊符号分隔。
表名 建议带上业务前缀，方便归类和区分。 例如：order_pay、product_spu等。
字段名称 建议使用统一的命名规则，方便理解和维护。 例如：status表示状态、create_time表示创建时间等。
索引名 主键：建议使用id或sys_no命名。 普通索引和联合索引：建议加ix_前缀。 唯一索引：建议加ux_前缀。
示例
# 表
-- 订单表
CREATE TABLE order_info (
id INT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL,
user_id INT NOT NULL,
order_amount DECIMAL(10,2) NOT NULL,
...
create_time DATETIME NOT NULL,
update_time DATETIME NOT NULL,
delete_status TINYINT(1) NOT NULL DEFAULT 0
);
-- 商品表
CREATE TABLE product_spu (
id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(128) NOT NULL,
product_code VARCHAR(32) UNIQUE,
...
create_time DATETIME NOT NULL,
update_time DATETIME NOT NULL,
delete_status TINYINT(1) NOT NULL DEFAULT 0
);
# 索引
-- 订单表索引
CREATE INDEX ix_order_no ON order_info (order_no);
CREATE INDEX ux_user_id ON order_info (user_id);
-- 商品表索引
CREATE UNIQUE INDEX ux_product_code ON product_spu (product_code);
总结
好的命名规范，可以提高数据库的可读性、维护性和易用性。建议大家在开发过程中，遵循上述规范，统一命名。
字段类型
数据库字段类型选择原则 在数据库设计中，选择合适的字段类型对数据库的性能、存储空间和维护效率都有着重要的影响。以下是一些选择字段类型的原则：
1. 尽可能选择占用存储空间小的字段类型
在满足正常业务需求的情况下，应从占用存储空间小的字段类型开始选择，逐步向上选择。例如：
如果字段只能存储1-10之间的数字，则可以选择tinyint类型，而不是int或bigint类型。 如果字符串字段的长度固定或差别不大，可以选择char类型，而不是varchar类型。
2. 根据数据特点选择合适的字段类型
如果是是否字段，可以选择bit类型。 如果是枚举字段，可以选择tinyint类型。 如果是主键字段，可以选择bigint类型。 如果是金额字段，可以选择decimal类型。 如果是时间字段，可以选择timestamp或datetime类型。
3. 考虑数据存储和查询的效率
在某些情况下，使用占用空间更大的字段类型可能会提高数据存储和查询的效率。例如，使用int类型存储1-10之间的数字，虽然只用1个字节就足够，但查询时需要进行类型转换，而使用tinyint类型则可以直接进行比较。 对于经常需要进行比较或排序的字段，建议使用固定长度的类型，如char或int类型。
4. 参考数据库的最佳实践
不同的数据库对字段类型的设计可能有所不同，建议参考数据库的最佳实践来选择合适的字段类型。例如，MySQL官方建议使用decimal类型存储金额字段，以避免精度损失。
总结
选择合适的字段类型需要综合考虑存储空间、性能、维护效率等因素，并结合具体的业务需求进行选择。遵循上述原则可以帮助您选择合适的字段类型，设计出高效、易用的数据库。
以下是一些额外的建议：
在设计表时，应充分考虑业务需求，避免出现冗余字段或字段类型选择不当的情况。 可以使用数据库建模工具来帮助您设计数据库表，并选择合适的字段类型。 在对数据库进行变更之前，应进行充分的测试，以避免影响正常业务。
字段长度
数据库字段长度的定义和注意事项
在数据库表设计中，字段长度的定义是必不可少的一环。它不仅影响着数据的存储空间，还影响着数据的存储和查询效率。
1. 字符串类型长度
在 MySQL 中，varchar 和 char 类型代表的是字符长度，而不是字节长度。 varchar(n) 表示该字段最多可以存储 n 个字符。 char(n) 表示该字段必须存储 n 个字符，不足 n 个字符时，会用空格填充。
2. 数值类型长度
除了 varchar 和 char 类型之外，MySQL 中其他类型都是以字节为单位定义长度的。 例如：int(n) 表示该字段可以存储 n 个字节的整数。 bigint(n) 表示该字段可以存储 n 个字节的大整数。
3. 特殊情况
bigint(n) 中的 n 并非指实际存储长度，而是指显示长度。 当字段值小于 n 时，会用 0 填充前面不足的位数（前提是该字段设置了 zerofill 属性）。 当字段值大于 n 时，则会按照实际长度显示。 部分 MySQL 客户端可能只显示 n 个字节的内容，即使实际长度大于 n。
4. 建议
在定义字段长度时，应充分考虑业务需求，避免浪费存储空间或造成数据截断。 对于字符串类型，建议使用 varchar 类型，以节省存储空间。 对于数值类型，建议根据实际情况选择合适的长度，避免数据溢出。 示例
用户姓名字段：可以使用 varchar(50) 存储，因为大多数用户的姓名不会超过 50 个字符。 订单金额字段：可以使用 decimal(10,2) 存储，其中 10 代表整数位数，2 代表小数位数。 主键字段：可以使用 bigint(20) 存储，以保证足够大的存储空间。 总结
数据库字段长度的定义需要根据实际情况进行选择，以满足业务需求并提高存储和查询效率
字段个数
数据库表字段个数限制
在数据库设计中，对表字段个数进行限制是非常必要的。过多的字段会带来以下问题：
降低数据库的查询效率：每个字段都需要存储空间和索引空间，字段越多，需要的存储空间和索引空间就越大，查询效率也会越低。 增加数据库的维护成本：字段越多，数据库的维护成本就越高，例如增加字段、删除字段、修改字段类型等操作都会变得更加复杂。 降低数据库的稳定性：过多的字段可能会导致数据库运行缓慢甚至崩溃。
建议：
每张表的字段个数尽量控制在20个以内。
如果确实需要使用大量字段，可以将一张表拆分成多张小表，这几张表的主键相同。
在拆分表时，应遵循以下原则：
每个表存储的字段应具有相同的业务逻辑。 每个表的字段个数应尽量均匀分布。 主键字段应尽量选择唯一性较强的字段。 示例：
一个用户表，可以拆分成以下几张表：
用户基本信息表：存储用户姓名、年龄、性别等基本信息。 用户联系方式表：存储用户电话号码、邮箱地址等联系方式。 用户订单表：存储用户订单信息。
总结：
对数据库表字段个数进行限制可以提高数据库的查询效率、降低维护成本和提高稳定性。建议根据实际情况进行拆分表，以满足业务需求并优化数据库性能。
约束
主键
数据库表主键设计
1. 主键的必要性
在数据库表设计中，主键是必不可少的。它具有以下功能：
唯一标识表中的每一行数据：主键的值必须是唯一的，不能重复。 提高数据查询效率：主键自带了主键索引，查询效率最高。 保持数据完整性：主键可以用于判断数据是否完整。
2. 主键的选择
主键建议选择与业务无关的值，减少业务耦合性，方便今后的扩展。 可以使用自增长的整数作为主键，但要注意在分布式数据库中，自增主键可能无法保证全局唯一性。 在分布式数据库中，建议使用雪花算法等外部算法生成主键，以保证全局唯一性。
3. 主键的设计原则
主键应尽量选择较短的字段，以节省存储空间。 主键应尽量选择不会频繁更改的字段，以免影响数据库性能。 主键应尽量选择与业务无关的字段，方便数据迁移和扩展。
4. 主键的应用场景
主键可以用于数据查询：通过主键可以快速找到指定的数据行。 主键可以用于数据更新：通过主键可以快速更新指定的数据行。 主键可以用于数据删除：通过主键可以快速删除指定的数据行。
5. 主键的注意事项
主键的值必须是唯一的，不能重复。 主键不能为 null。 主键不能频繁更改。
示例
用户表：可以使用用户 ID 作为主键。 订单表：可以使用订单 ID 作为主键。 商品表：可以使用商品 ID 作为主键。
总结
主键是数据库表中非常重要的一个字段，它不仅可以提高数据查询效率，还可以保证数据完整性。在设计主键时，应遵循上述原则，以满足业务需求并优化数据库性能。
关于一对一关系
“一对一”关系中，如果两个表之间存在强依赖关系，可以将子表的主键设置为父表的主键。这样可以简化表结构，提高数据查询效率。
例如，用户表和用户扩展表之间存在一对一的关系，用户扩展表的主键可以设置为用户表的主键。这样，在查询用户扩展信息时，可以直接通过用户 ID 进行查询。
但是，如果两个表之间不存在强依赖关系，建议将两个表的主键设置为不同的字段。这样可以提高表的独立性和可扩展性。
例如，订单表和订单详情表之间存在一对一的关系，订单详情表的主键可以设置为订单 ID 加上一个自增长的字段。这样，在查询订单详情信息时，可以通过订单 ID 进行查询，也可以通过订单详情 ID 进行查询。
NOT NULL
数据库字段设置 NOT NULL 和默认值 在数据库表设计中，设置字段是否允许为 NULL 是一个重要的决定。在大多数情况下，建议将字段设置为 NOT NULL，并设置默认值。
设置 NOT NULL 的原因
节省存储空间：在 InnoDB 存储引擎中，NULL 值需要额外的空间存储，设置为 NOT NULL 可以节省存储空间。 提高查询效率：NULL 值会导致索引失效，降低查询效率。 提高数据完整性：NOT NULL 可以确保字段的值不能为空，提高数据完整性。
设置默认值的原因
避免插入数据时报错：如果字段设置为 NOT NULL，而没有设置默认值，则在插入数据时，如果该字段没有赋值，就会报错。 方便数据迁移：在新增字段时，如果设置了默认值，则在进行数据迁移时，可以避免新字段出现 NULL 值。
示例
用户姓名字段：可以设置为 NOT NULL，并设置默认值为空字符串。 用户年龄字段：可以设置为 NOT NULL，并设置默认值为 0。 订单金额字段：可以设置为 NOT NULL，并设置默认值为 0.00。
注意事项
在设置默认值时，应考虑业务需求，选择合适的默认值。
如果字段的值可能为 NULL，则应考虑使用其他方法来处理，例如：
使用可空字段类型 使用特殊值表示 NULL
总结 在大多数情况下，建议将字段设置为 NOT NULL，并设置默认值。这样做可以节省存储空间、提高查询效率、提高数据完整性，并方便数据迁移。
外键
MySQL 外键使用建议 外键的作用
外键是数据库中用于维护数据一致性和完整性的约束。它通过将一张表中的字段与另一张表中的字段进行关联，来保证数据之间的逻辑关系。
外键的优点
确保数据的一致性：外键可以防止数据出现矛盾或错误。 提高数据完整性：外键可以保证数据不会被意外删除或修改。 简化数据操作：外键可以简化数据查询和更新操作。
外键的缺点
降低性能：外键可能会降低数据库的插入、更新和删除性能。 增加复杂性：外键会增加数据库设计的复杂性。 使用建议
在需要保证数据一致性和完整性的情况下，建议使用外键。 在对性能要求较高的系统中，可以考虑不使用外键，而是通过应用程序代码来保证数据一致性和完整性。 如果需要使用外键，应尽量减少外键的数量和复杂度。
示例
订单表和订单详情表之间可以建立外键关系，以保证订单数据的一致性和完整性。 用户表和用户角色表之间可以建立外键关系，以保证用户权限的一致性和完整性。
替代方案
应用程序代码：可以使用应用程序代码来检查数据的一致性和完整性。 触发器：可以使用触发器来保证数据的一致性和完整性。
总结
外键是一种有用的工具，可以帮助您维护数据库数据的一致性和完整性。但是，外键也可能降低性能和增加复杂性。因此，在使用外键之前，应仔细考虑您的需求。
唯一索引
MySQL 唯一索引使用建议
1. 唯一索引简介
唯一索引是一种数据库约束，它确保数据库表中每个字段的组合都是唯一的。
2. 唯一索引优势
保证数据唯一性：可以防止数据重复插入。 提高数据完整性：可以确保数据的准确性。 加速数据查询：可以利用唯一索引快速找到指定的数据行。
3. 使用场景
唯一标识：例如：用户 ID、商品 ID 等。 业务主键：例如：订单号、发票号等。 组合字段：例如：姓名、身份证号码等。
4. 注意事项
唯一索引字段不能包含 null 值，否则唯一性约束会失效。 联合唯一索引的字段顺序会影响索引的效率，应根据查询条件进行排序。 创建唯一索引会增加数据库的存储空间和维护成本。
5. 示例
用户表：可以使用唯一索引约束用户 ID 的唯一性。 订单表：可以使用唯一索引约束订单号的唯一性。 商品表：可以使用联合唯一索引约束商品的名称、规格和型号的唯一性。
6. 唯一索引失效
以下情况会导致唯一索引失效：
唯一索引字段包含 null 值。 使用了 like 或 != 等模糊查询操作。 使用了 COUNT() 或 AVG() 等聚合函数。
总结
唯一索引是一种非常有用的数据库约束，可以保证数据唯一性、提高数据完整性和加速数据查询。在设计数据库表时，应根据业务需求合理使用唯一索引。
性能优化
存储引擎
MySQL存储引擎选择建议
1. MySQL 8 之前的版本
在 MySQL 8 之前的版本中，默认的存储引擎是 MyISAM，而 InnoDB 是可选的存储引擎。两种存储引擎各有优缺点：
MyISAM
优点：查询速度快，支持全文索引，表结构简单。 缺点：不支持事务和外键，不适合写多读少的场景。 InnoDB
优点：支持事务和外键，支持行锁，数据安全性高，适合写多读少的场景。 缺点：查询速度比 MyISAM 慢，不支持全文索引。 选择建议
读多写少的表，建议使用 MyISAM 存储引擎。 写多读多的表，建议使用 InnoDB 存储引擎。
2. MySQL 8 及更高版本
在 MySQL 8 及更高版本中，默认的存储引擎改为 InnoDB。这是因为 InnoDB 在性能、功能和安全性方面都得到了很大的提升，已经能够满足大多数应用的需求。
InnoDB 的改进
查询性能：InnoDB 的查询性能得到了大幅提升，与 MyISAM 的差距已经很小。
全文索引：InnoDB 在 MySQL 5.6 版本中开始支持全文索引。
其他功能：InnoDB 还支持许多其他功能，例如：
外键行锁事务热备份选择建议
在 MySQL 8 及更高版本中，建议直接使用默认的 InnoDB 存储引擎，无需额外修改存储引擎。
3. 特殊情况
在一些特殊情况下，可能仍然需要使用 MyISAM 存储引擎，例如：
需要使用全文索引，并且对查询速度要求较高。 需要使用非常大的表，而 InnoDB 存储引擎无法满足需求。
总结
在大多数情况下，建议使用 InnoDB 存储引擎。InnoDB 在性能、功能和安全性方面都得到了很大的提升，已经能够满足大多数应用的需求。 MySQL 官方已经不再积极开发 MyISAM 存储引擎，建议用户在新的应用中尽量使用 InnoDB 存储引擎。
索引
数据库索引设计建议
1. 索引的必要性
索引是数据库中用于提高数据查询效率的一种数据结构。它可以帮助数据库快速找到指定的数据行。
2. 索引的类型
主键索引：主键索引是唯一索引，用于保证数据的一致性和完整性。普通索引：普通索引是非唯一索引，用于提高数据查询效率。联合索引：联合索引是多个字段组合而成的索引，可以提高多字段查询效率。
3. 索引设计原则
索引字段应该经常出现在 where 子句中。 索引字段应该具有较高的区分度。 索引字段不宜过多，建议单表索引个数不要超过 5 个。 对于数据重复率非常高的字段，不建议单独创建索引。
4. 索引失效
索引失效是指索引无法被数据库使用的现象。索引失效的原因有很多，常见的原因包括：
查询条件不满足最左匹配原则。 使用了 like 或 != 等模糊查询操作。 使用了 COUNT() 或 AVG() 等聚合函数。
5. 索引优化
尽量使用覆盖索引：覆盖索引是指索引包含查询的所有字段，这样可以避免回表操作。 使用合适的数据类型：使用合适的字段类型可以提高索引的效率。 定期维护索引：定期重建或分析索引可以保持索引的最佳状态。
6. 示例
商品表：可以创建 spu_id 和 brand_id 的普通索引，以提高商品查询效率。 用户表：可以创建 name 和 age 的联合索引，以提高用户查询效率。
总结
索引是数据库中非常重要的一个概念，可以显著提高数据库查询效率。在设计索引时，应遵循上述原则，以满足业务需求并优化数据库性能。
关于联合索引的最左匹配原则
最左匹配原则指的是，在使用联合索引进行查询时，查询条件必须从联合索引的最左边的字段开始匹配，如果查询条件不满足最左匹配原则，则索引可能无法被使用。
例如，假设有一个联合索引 (a, b)，则以下查询可以利用索引：
SELECT * FROM table WHERE a = 1 AND b = 2;
SELECT * FROM table WHERE a = 1;
但以下查询无法利用索引：
SELECT * FROM table WHERE b = 2;
SELECT * FROM table WHERE a > 1;
在设计联合索引时，应考虑业务需求，选择合适的字段顺序，以提高索引的利用率。
排序规则
MySQL 字符排序规则选择建议
1. 字符排序规则简介
字符排序规则是指对字符进行排序的规则。MySQL 支持多种字符排序规则，常见的有：
utf8mb4_general_ci：不区分大小写，适用于不区分大小写的比较操作。 utf8mb4_bin：区分大小写，适用于区分大小写的比较操作。
2. 字符排序规则选择建议
业务场景：如果业务场景需要区分大小写，则选择 utf8mb4_bin 排序规则。如果业务场景不需要区分大小写，则选择 utf8mb4_general_ci 排序规则。 性能：utf8mb4_general_ci 排序规则的性能略好于 utf8mb4_bin 排序规则。
3. 其他注意事项
字符集和排序规则：字符排序规则与字符集相关，应选择与字符集匹配的排序规则。 索引：如果使用索引进行查询，则应选择与索引一致的排序规则。
4. 示例
用户表：如果需要区分用户登录名的大小写，则可以使用 utf8mb4_bin 排序规则。 商品表：如果商品名称不区分大小写，则可以使用 utf8mb4_general_ci 排序规则。
5. 字符排序规则比较
排序规则区分大小写性能适用场景utf8mb4_general_ci否优不区分大小写的比较操作utf8mb4_bin是良区分大小写的比较操作
总结
字符排序规则选择非常重要，会影响数据库的查询效率和结果。在选择字符排序规则时，应充分考虑业务需求和性能因素。
关于区分大小写
区分大小写是指对字符的大小写进行区分。例如，“A” 和 “a” 是两个不同的字符。
在 MySQL 中，可以使用 COLLATE 参数来设置字符排序规则。例如：
CREATE TABLE `user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`username` varchar(20) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`id`)
);
示例中，username 字段的排序规则设置为 utf8mb4_bin，区分大小写。
关于索引
如果使用索引进行查询，则应选择与索引一致的排序规则。例如，如果索引的排序规则是 utf8mb4_bin，则查询条件也应区分大小写。
特殊字段
时间字段
数据库时间字段类型选择建议
在数据库设计中，时间字段的类型选择非常重要。选择合适的类型可以提高数据查询效率、节省存储空间，并避免数据错误。
1. 时间字段类型
MySQL 支持以下时间字段类型：
DATE：用于保存日期，例如：2023-11-16。 DATETIME：用于保存日期和时间，例如：2023-11-16 12:34:56。 TIMESTAMP：用于保存日期和时间，并带有时区信息，例如：2023-11-16 12:34:56+08:00。 VARCHAR：可以保存任何字符串，包括日期和时间，例如：2023-11-16 12:34:56。
2. 类型选择建议
日期：如果只需要保存日期，建议使用 DATE 类型。 日期和时间：如果需要保存日期和时间，建议使用 DATETIME 类型。 日期和时间，并带有时区信息：如果需要保存日期和时间，并带有时区信息，建议使用 TIMESTAMP 类型。 特殊情况：如果需要保存特殊格式的日期和时间，例如：时间戳，可以使用 VARCHAR 类型。
3. 其他注意事项
默认值：建议不要将时间字段的默认值设置为 ‘0000-00-00 00:00:00’，因为它可能导致数据转换错误。 时区：如果使用 TIMESTAMP 类型，应注意时区问题。
4. 示例
用户表：可以使用 DATETIME 类型保存用户的注册时间。 订单表：可以使用 DATETIME 类型保存订单的创建时间和完成时间。 日志表：可以使用 TIMESTAMP 类型保存日志的时间。
总结
时间字段类型选择非常重要，应根据实际需求选择合适的类型。选择合适的类型可以提高数据查询效率、节省存储空间，并避免数据错误。
金额字段
MySQL 浮点数类型选择建议 在 MySQL 数据库中，有多个字段可以表示浮点数，包括 float、double 和 decimal。
1. 浮点数类型简介
FLOAT：单精度浮点数，占用 4 个字节存储空间，有效数字为 7 位。 DOUBLE：双精度浮点数，占用 8 个字节存储空间，有效数字为 15 位。 DECIMAL：定点类型，占用存储空间与定义精度相关，可精确表示数值。
2. 类型选择建议
对于需要精确计算的场景，例如：金额计算，建议使用 DECIMAL 类型。 对于对精度要求不高的场景，例如：科学计算，可以使用 FLOAT 或 DOUBLE 类型。
3. DECIMAL 类型详解
DECIMAL 类型可以定义两个参数：
M：表示整数和小数部分的总位数，范围为 1 到 65。 D：表示小数部分的位数，范围为 0 到 M。 例如，DECIMAL(10,2) 表示总位数为 10，小数位数为 2，可表示的数值范围为 -99999999.99 到 99999999.99。
4. 其他注意事项
DECIMAL 类型在存储时会进行四舍五入，例如：123.456 会被四舍五入为 123.46。 DECIMAL 类型在比较时会精确比较，例如：123.45 和 123.4500 是相等的。
5. 示例
订单表：可以使用 DECIMAL 类型保存订单金额。 产品表：可以使用 DECIMAL 类型保存产品价格。 用户表：可以使用 DECIMAL 类型保存用户余额。
总结
在 MySQL 数据库中，选择合适的浮点数类型非常重要。DECIMAL 类型可以精确表示数值，适合需要精确计算的场景。FLOAT 和 DOUBLE 类型占用存储空间较小，适合对精度要求不高的场景。
json字段
MySQL JSON字段类型使用建议
1. JSON字段类型简介
MySQL 5.7.8 及更高版本支持 JSON 数据类型，可以用于存储 JSON 格式的结构化数据。
2. JSON字段类型优势
灵活：可以存储任意结构的 JSON 数据，例如：对象、数组、字符串、数字等。 方便：可以使用 JSON 函数直接操作 JSON 数据，例如：添加、删除、修改、查询等。 高效：MySQL 对 JSON 数据进行了优化，查询效率较高。
3. 使用场景
存储不固定的数据结构：例如：用户设置、查询条件等。 存储复杂的数据结构：例如：树形结构、图结构等。 存储半结构化数据：例如：XML 数据、JSON 数据等。
4. 示例
用户表：可以使用 JSON 类型保存用户的个性化设置。 订单表：可以使用 JSON 类型保存订单的详细信息。 日志表：可以使用 JSON 类型保存日志的详细信息。
5. 其他注意事项
JSON 字段类型不能设置默认值。 JSON 字段类型不能用于主键或索引。 JSON 字段类型在使用时需要进行类型转换。
6. JSON 函数示例
JSON_EXTRACT()：用于提取 JSON 数据中的某个字段值。 JSON_SET()：用于修改 JSON 数据中的某个字段值。 JSON_ARRAY_APPEND()：用于向 JSON 数组中添加元素。
总结
JSON 字段类型是一种非常灵活的数据类型，可以用于存储各种结构化的数据。在设计数据库表时，如果需要存储不固定的数据结构、复杂的数据结构或半结构化数据，可以考虑使用 JSON 字段类型。
大字段
MySQL 大字段存储建议
1. 大字段简介
大字段是指占用较多存储空间的字段，例如：用户评论、合同文档等。
2. 大字段存储问题
浪费存储空间：如果使用不合适的字段类型，可能会浪费存储空间。 降低性能：大字段会导致数据库操作的性能下降。
3. 大字段存储建议
根据实际情况选择合适的字段类型： 可控长度的大字段：如果字段长度可控，建议使用 VARCHAR 类型。 不可控长度的大字段：如果字段长度不可控，建议使用 TEXT 或 BLOB 类型。 分离存储：对于非常大的字段，可以考虑将数据存储在外部文件系统中，并在数据库中保存文件路径。 压缩：可以对大字段进行压缩，以节省存储空间。
4. 示例
用户评论：可以使用 VARCHAR(500) 类型存储用户评论。 合同文档：可以使用 MEDIUMTEXT 或 LONGBLOB 类型存储合同文档。 图片：可以使用 BLOB 类型存储图片。
5. 其他注意事项
索引：大字段不宜创建索引，否则会降低查询性能。 字符集：对于存储文本的大字段，应选择合适的字符集，避免出现乱码问题。
6. 分离存储
对于非常大的字段，例如：合同文档、图片等，可以考虑将数据存储在外部文件系统中，并在数据库中保存文件路径。
例如，可以将合同文档存储在 /data/contracts 目录中，并在数据库中保存以下数据：
CREATE TABLE `contract` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`path` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
示例中，contract 表保存了合同的名称和文件路径。
7. 压缩
可以对大字段进行压缩，以节省存储空间。
例如，可以使用 MySQL 的 COMPRESSION 选项来压缩 BLOB 类型的数据：
CREATE TABLE `image` (
`id` bigint NOT NULL AUTO_INCREMENT,
`data` BLOB COMPRESSION 'zlib',
PRIMARY KEY (`id`)
);
示例中，image 表中的 data 字段使用 ZLIB 算法进行压缩。
总结
大字段存储需要根据实际情况选择合适的方法。建议根据字段长度、存储空间和性能要求等因素综合考虑。
其他
字符集
MySQL 字符集选择建议
1. 字符集简介
字符集是指一组字符的集合，每个字符都对应一个编码。MySQL 支持多种字符集，常见的有：
latin1：西欧字符集，不包含中文。 GBK：中文字符集，不包含国际通用字符。 UTF-8：通用字符集，支持大部分语言。 UTF8MB4：UTF-8 的扩展版本，支持所有 Unicode 字符。
2. 字符集选择建议
国际化项目：建议使用 UTF8MB4 字符集，可以支持所有语言。 中文项目：建议使用 UTF8MB4 字符集，可以支持中文和 emoji 表情。 存储空间有限：如果存储空间有限，可以考虑使用 UTF-8 字符集，但需要注意 emoji 表情可能无法存储。
3. 其他注意事项
数据库和表的字符集应保持一致。 客户端的字符集应与数据库的字符集一致，避免出现乱码问题。
4. 示例
国际化网站：可以使用 UTF8MB4 字符集保存用户姓名、地址等信息。 中文网站：可以使用 UTF8MB4 字符集保存文章标题、内容等信息。 移动应用：可以使用 UTF8MB4 字符集保存用户昵称、头像等信息。
5. 字符集转换
如果需要将数据库的字符集进行转换，可以使用以下工具：
MySQL自带的 mysqldump 和 mysqlimport 工具 第三方工具，例如：Navicat
总结
字符集选择非常重要，会影响数据库的存储空间、性能和兼容性。在选择字符集时，应充分考虑业务需求和技术因素。
冗余字段
MySQL 冗余字段设计建议
1. 冗余字段简介
冗余字段是指在数据库的多个表中重复出现的字段。
2. 冗余字段优势
提高查询性能：可以减少表连接操作，提高查询效率。 简化查询逻辑：可以避免复杂的表连接操作，简化查询逻辑。
3. 冗余字段劣势
浪费存储空间：会增加数据库的存储空间。 数据不一致：需要维护多个字段的一致性，否则会导致数据不一致问题。
4. 冗余字段应用场景
查询频率高的字段：对于查询频率高的字段，可以考虑冗余到其他表中，以提高查询性能。 关联查询复杂：如果关联查询比较复杂，也可以考虑冗余字段来简化查询逻辑。
5. 冗余字段设计建议
评估收益和成本：在使用冗余字段之前，应充分评估收益和成本。 选择合适的字段：选择合适的字段进行冗余，避免冗余过多的字段。 维护数据一致性：需要维护冗余字段的一致性，避免数据不一致问题。
6. 示例
订单表：可以冗余用户姓名、收货地址等信息，以提高订单查询性能。 商品表：可以冗余商品分类名称、品牌名称等信息，以简化商品查询逻辑。
7. 其他注意事项
冗余字段不宜用于主键或索引：冗余字段可能会导致主键或索引冗余，降低数据库性能。 冗余字段应定期更新：冗余字段应定期更新，以保证数据一致性。
8. 数据一致性问题
冗余字段可能会导致数据不一致问题。例如，如果用户名称修改了，则需要同时修改订单表中的冗余字段。
为了解决数据一致性问题，可以采取以下措施：
使用触发器：可以使用触发器自动更新冗余字段。 应用程序更新：在应用程序中更新冗余字段，确保数据一致性。
总结
冗余字段是一种提高查询性能的有效方法，但同时也存在一些问题。在使用冗余字段之前，应充分评估收益和成本，并采取措施保证数据一致性。
注释
数据库表设计注释的重要性 1. 注释简介
注释是数据库表和字段的说明文字，可以帮助用户理解数据库结构和字段含义。
2. 注释的重要性
提高可读性：注释可以提高数据库表的可读性，方便用户理解数据库结构。 降低维护成本：注释可以降低数据库表的维护成本，方便用户查找和修改字段。 避免数据错误：注释可以避免数据错误，帮助用户正确使用数据库。
3. 注释建议
表注释：表注释应说明表的用途和功能。 字段注释：字段注释应说明字段的含义、数据类型、取值范围等信息。 状态字段注释：对于状态字段，应详细说明每个状态值的含义。 示例：
CREATE TABLE `sys_dept` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(30) NOT NULL COMMENT '部门名称',
`pid` bigint NOT NULL COMMENT '上级部门ID',
`valid_status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '有效状态 1:有效 0:无效',
`create_user_id` bigint NOT NULL COMMENT '创建人ID',
`create_user_name` varchar(30) NOT NULL COMMENT '创建人名称',
`create_time` datetime(3) DEFAULT NULL COMMENT '创建时间',
`update_user_id` bigint DEFAULT NULL COMMENT '修改人ID',
`update_user_name` varchar(30) DEFAULT NULL COMMENT '修改人名称',
`update_time` datetime(3) DEFAULT NULL COMMENT '修改时间',
`is_del` tinyint(1) DEFAULT '0' COMMENT '是否删除 1：已删除 0：未删除',
PRIMARY KEY (`id`) USING BTREE,
KEY `index_pid` (`pid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='部门';
示例中，对表和字段都进行了详细的注释。
4. 其他注意事项
注释应简洁明了：注释应简洁明了，避免使用过于复杂或模糊的语言。 注释应保持更新：当数据库结构或字段含义发生变化时，应及时更新注释。
5. 总结
数据库表设计注释非常重要，可以提高数据库的可读性、维护性和安全性。在设计数据库表时，应认真编写注释，并定期更新注释。