作为一个程序员，经常会和数据库打交道，我最近在面试的时候，也遇到了这样的问题。谁都知道，建一张表，不过是敲几行SQL的事。一个CREATE，几个字段，回车一按，仿佛大功告成。可谁又知道，这轻飘飘的一敲，竟如刻碑立石，百年难改？今日图快的一笔，明日便成了千军万马绕道的深渊；此刻偷懒的一念，终将化作系统崩塌时的惊雷。多少人以为，数据库不过是铁皮柜子，扔进去便是。殊不知，那字段命名、索引设计、主键选择、字符集设定，无一不是刀锋上的舞步。一步踏错，轻则查询如牛负重，重则数据错乱、系统瘫痪，乃至祸延数载，无人能解。这建表的笔，原不该如此轻率。它写的不是代码，是契约；定的不是结构，是命运。而我们，竟常以儿戏视之。所以今天我将于此去整理自己在这建表上面走过的经验。

一、建表操作

1.命名相关注意事项：库名、表名、字段名，使用小写字母和下划线_分割，最好不超过12个字符，默认的是支持到64个字符，命名最好和业务结合，做到见表知意，使用名词概括。
2.默认使用的是InnoDB数据库引擎（mysql），支持事务，锁，高并发性能好
3.注意数据库编码格式，使用utf8mb4可以支持emoji的存储
4.注意一个表的字段数不要过多，建议40个，过多需要做业务上的垂直分表

二、字段相关

1.整型的使用不显示设置长度，在业务层或者前端来设置显示长度
2.存储精度浮点数，使用DICIMEL而不是FLOAT或者DOUBLE，比如金融计算、货币值；FLOAT和DOUBLE这两种类型属于浮点数类型，它们提供了广泛的数值范围但是牺牲了精度，从而导致会有舍入误差
3.字段需要加上COMMENT注释来说明字段的意义
4.字段最好不要允许空值存在，如果应用程序代码中忘记检查空值可能导致运行时错误或未预期的行为
5.TIMESTAMP时间戳的范围是到2038年，DATETIME或者DATE来实现时间存储
6.短数据类型使用TINYINT存储（-128 到 127）
7.IPV4使用INT UNSIGNED (使用INET_ATON() 将IP字符串转为整数，INET_NTOA() 将整数转回IP字符串)， IPV6使用VARBINARY作为字段类型
8.涉及时间更新的字段需要设置on update来更新属性

-- 插入数据 (自动转换)
INSERT INTO user_ips (ipv4_addr) VALUES (INET_ATON('192.168.1.1'));

-- 查询并转换回可读格式
SELECT INET_NTOA(ipv4_addr) AS ip FROM user_ips;

三、索引相关

1.建立索引需要使用自增ID作为主键，不能使用随机性强的作为主键，会导致InnoDB内部page分裂和大量随机I/O,性能下降
2.单表索引最好不要超过5个，字段索引数不超过5个
3.利用覆盖索引来进行查询操作，避免回表，另外创建组合索引的时候，需要遵循最左原则。
4.需要注意字段不同造成的隐式转换而使得索引失效（当查询条件中的数据类型与表中对应列的数据类型不一致时，数据库系统可能会尝试将其中一个值转换为另一个的类型）
5.经常更新的字段不适合建立索引，因为维护索引也需要成本

四、其他整理

1.单表的数据量不超过500万行，ibd存储文件大小不超过2g
2.水平分表时，一般数据使用取模，日志、报表可以使用日期来区分
3.一个数据库实例中，建议或限制创建的表总数不超过500张
4.在执行alter插入数据表时，需要先判断表数据量，对于百万级别的数据表需要在业务低峰期执行，因为alter操作会产生表锁，期间阻塞该表的所有写入
5.SELECT语句必须指定具体字段，一是为了节省传输，提高性能，此外，数据表字段一旦更新，但model层没有来得及更新的话，系统会报错
6.在编写 INSERT 语句时，强烈建议明确指定字段名称
7.批量插入时，数据一次性不要超过5000个，过多会引起主从同步延迟变大
8.组合查询结果时，最好不超过5个子句，UNION将多个 SELECT 语句的结果集合并，并自动去除重复的行，而UNION ALL将多个 SELECT 语句的结果集简单地堆叠在一起，不去除重复行。
9.in 值列表限制在500以内，例如 select… where userid in(….500个以内…)，可以减少底层扫描，减轻数据库压力。
10.查找语句尽量走索引
11.WHERE条件里，等号左右的数据类型必须一致，不然会引起隐式转换，索引失效
12.索引列尽量不使用函数或者表达式，会导致索引失效
13.生产数据库中强烈不推荐在大表执行全表扫描，查询数据量不要超过表行数的25%，否则可能导致无法使用索引
14.where子句中禁止只使用全模糊的LIKE条件进行查找，如like ‘%abc%’，必须有其他等值或范围查询条件，否则可能导致无法使用索引
15.分页查询，当limit起点较高时，可先用过滤条件进行过滤。如 select a,b,c from t1 limit 10000,20; 优化为 select a,b,c from t1 where id>10000 limit 20;

常用的一些mysql函数

字符串函数

1. CONCAT()
   • 用途：连接两个或多个字符串。
   • 示例：SELECT CONCAT('Hello', ' ', 'World') AS Greeting;
2. LENGTH()
   • 用途：返回字符串的长度（字节数）。
   • 示例：SELECT LENGTH('Hello World');
3. LOWER() / UPPER()
   • 用途：分别将字符串转换为小写/大写。
   • 示例：SELECT LOWER('HELLO'), UPPER('world');
4. SUBSTRING()
   • 用途：从字符串中提取子串。
   • 示例：SELECT SUBSTRING('Hello World', 7); 或 SELECT SUBSTRING('Hello World', 1, 5);

数值函数

1. ABS()
   • 用途：返回一个数的绝对值。
   • 示例：SELECT ABS(-10);
2. ROUND()
   • 用途：四舍五入到指定的小数位数。
   • 示例：SELECT ROUND(123.456, 2);
3. RAND()
   • 用途：生成一个介于0和1之间的随机浮点数。
   • 示例：SELECT RAND();

日期和时间函数

1. NOW()
   • 用途：返回当前日期和时间。
   • 示例：SELECT NOW();
2. DATE()
   • 用途：提取日期部分。
   • 示例：SELECT DATE('2025-07-29 02:18:00');
3. TIMESTAMPDIFF()
   • 用途：计算两个日期之间的时间差。
   • 示例：SELECT TIMESTAMPDIFF(YEAR, '2020-01-01', '2025-01-01');

转换函数

1. CAST() 和 CONVERT()
   • 用途：用于类型转换。
   • 示例：
     • 使用 CAST: SELECT CAST('123' AS SIGNED);
     • 使用 CONVERT: SELECT CONVERT('2025-01-01', DATE);

条件判断函数

1. IF()
   • 用途：根据条件返回不同的值。
   • 示例：SELECT IF(1 > 0, 'Yes', 'No');
2. CASE WHEN THEN ELSE END
   • 用途：更复杂的条件判断。
   • 示例：sql深色版本SELECT CASE WHEN age < 18 THEN 'Minor' WHEN age >= 18 AND age <= 65 THEN 'Adult' ELSE 'Senior' END AS AgeGroup FROM users;

网络地址函数

1. INET_ATON() / INET_NTOA()
   • 用途：IPv4地址与整数间的转换。
   • 示例：
     • SELECT INET_ATON('192.168.0.1');
     • SELECT INET_NTOA(3232235521);
2. INET6_ATON() / INET6_NTOA()
   • 用途：IPv6地址与二进制格式间的转换。
   • 示例：
     • SELECT INET6_ATON('2001:db8::1');
     • SELECT INET6_NTOA(INET6_ATON('2001:db8::1'));