初级DBA必备：字段类型与字符集详解

2.1 常见字符串类型及应用场景

2.1.1 核心字符串类型解析

• CHAR与VARCHAR：均用于存储字符串，但特性差异显著。CHAR为固定长度（0-255字节），存储时若内容长度不足，会自动用空格填充；VARCHAR为可变长度（0-65535字节），仅占用实际内容所需空间，更节省存储。
• TEXT与BLOB：适用于大内容存储。TEXT专为长文本设计（如文章、日志）；BLOB则用于二进制数据（如图片、音频文件），二者均不支持索引前缀。
• ENUM与SET：枚举类类型。ENUM允许从预设列表中选择唯一值（如性别：男/女）；SET允许从预设列表中选择一个或多个值（如兴趣：读书/运动/音乐）。

2.1.2 实践验证：类型特性对比

实验1：CHAR与VARCHAR的差异

创建测试表：

CREATE TABLE aaa(one CHAR(10), two VARCHAR(10));

• 长度限制验证：写入超过10个字符的数据时，二者均会触发错误：

  INSERT INTO aaa(one) VALUES ('abcabcabcabc'); -- 报错
  INSERT INTO aaa(two) VALUES ('abcabcabcabc'); -- 报错
  

• 正常长度写入：10个字符以内可正常存储：

  INSERT INTO aaa(one) VALUES ('aaa');
  INSERT INTO aaa(two) VALUES ('aaa');
  

• 空格处理差异：

  INSERT INTO aaa VALUES ('a  ', 'a  '); -- 均写入带2个尾空格的字符串
  SELECT CONCAT(one, '|'), CONCAT(two, '|') FROM aaa; -- 查看结果
  

  结果显示：CHAR会自动去除尾部空格，VARCHAR则保留尾部空格。

实验2：ENUM类型的限制与索引特性

创建表并插入数据：

CREATE TABLE bbb(name VARCHAR(10), gender ENUM('男', '女'));
INSERT INTO bbb VALUES ('a', '男'), ('b', '女');

• 非法值插入限制：写入枚举列表外的值会报错：

  INSERT INTO bbb VALUES ('c', '其他'); -- 报错（'其他'不在预设列表中）
  

• 索引特性：每个枚举值对应唯一索引（从1开始），可通过索引查询：

  SELECT * FROM bbb WHERE gender = 1; -- 等价于 gender = '男'
  SELECT * FROM bbb WHERE gender = 2; -- 等价于 gender = '女'
  

实验3：SET类型的多值存储

创建表并插入数据：

CREATE TABLE ccc(one SET('a', 'b', 'c', 'd'));
INSERT INTO ccc VALUES ('a,b,c'), ('a,a'), ('b,d,b'); -- 重复值会自动去重

• 非法值限制：写入列表外的值会报错：

  INSERT INTO ccc VALUES ('a,b,e'); -- 报错（'e'不在预设列表中）
  

2.1.3 类型选择指南

选择字符串类型需结合长度稳定性、存储效率、查询需求：

• 固定长度且短内容（如手机号、身份证号）：优先用CHAR（查询更快）。
• 长度不固定且可能较长（如用户名、地址）：优先用VARCHAR（节省空间）。
• 超长篇文本（如文章内容）：用TEXT；二进制数据：用BLOB。
• 有限可选值场景：单选用ENUM，多选用SET（需注意二者均不适合高频变更的选项列表）。

2.2 整数类型深度解析及应用场景

2.2.1 核心整数类型特性

整数类型的选择需权衡取值范围与存储开销，以下为常见类型对比：

2.2.2 实践验证：整数类型的范围限制

实验：INT类型的有符号与无符号差异

• 有符号INT：

  CREATE TABLE int_test(id INT);
  INSERT INTO int_test VALUES (2147483647); -- 正常插入（最大值）
  INSERT INTO int_test VALUES (2147483648); -- 报错（超出范围）
  

• 无符号INT（需显式声明）：

  CREATE TABLE int_test_unsigned(id INT UNSIGNED);
  INSERT INTO int_test_unsigned VALUES (2147483648); -- 正常（无符号最小值）
  INSERT INTO int_test_unsigned VALUES (4294967295); -- 正常（无符号最大值）
  INSERT INTO int_test_unsigned VALUES (4294967296); -- 报错（超出范围）
  

2.2.3 类型选择原则

• 遵循“最小够用”原则：如年龄用TINYINT UNSIGNED（0-255完全覆盖人类年龄范围），避免用INT浪费空间。
• 高频查询字段优先选小类型：减少IO开销，提升查询效率。
• 注意符号需求：如计数器、ID等非负数据，用UNSIGNED扩展取值范围（如INT UNSIGNED比INT多一倍正数值空间）。

2.3 浮点数与定点数：差异及场景选择

2.3.1 核心类型解析

• FLOAT：单精度浮点数（32位），精度约7位十进制数，适合对精度要求低但性能敏感的场景（如游戏数值），存在舍入误差。
• DOUBLE：双精度浮点数（64位），精度约15位十进制数，适用于科学计算等中等精度场景，仍可能有误差。
• DECIMAL：定点数，通过DECIMAL(M,D)定义（M为总位数，D为小数位数），无舍入误差，适合高精度场景（如金融交易），但计算性能略低。

2.3.2 实践验证：精度与范围差异

实验1：DECIMAL的范围限制

CREATE TABLE decimal_test(aaa DECIMAL(5,2)); -- 总长度5位，小数2位（范围：-999.99 ~ 999.99）
INSERT INTO decimal_test VALUES (-999.99), (999.99); -- 正常插入
INSERT INTO decimal_test VALUES (-1000.99); -- 报错（超出整数部分范围）
INSERT INTO decimal_test VALUES (2.343); -- 自动四舍五入为2.34
create table decimal_test_1(a decimal); -- 当DECIMAL类型不指定精度和刻度，默认就是decimal(10,0)
show create table decimal_test_1;

实验2：浮点数与定点数的精度差异

CREATE TABLE float_decimal_test(a FLOAT, b DECIMAL(8,4)); -- 对比两者精度
INSERT INTO float_decimal_test VALUES (7777.7777, 7777.7777), (66.6666, 66.6666);
SELECT * FROM float_decimal_test;

结果显示：FLOAT会因精度限制出现近似值，DECIMAL则完全精确。

2.3.3 类型选择指南

• 金融/支付场景：强制用DECIMAL（如DECIMAL(10,2)存储金额），避免精度损失。
• 科学计算/游戏：用DOUBLE（平衡精度与性能）。
• 低精度场景（如温度、评分）：用FLOAT（节省空间）。

2.4 时间类型详解及实践应用

2.4.1 核心时间类型特性

2.4.2 实践验证：时间类型的特性

实验1：TIME的范围限制

CREATE TABLE time_test(a TIME);
INSERT INTO time_test VALUES ('20:08:08'); -- 正常插入
INSERT INTO time_test VALUES ('839:08:08'); -- 报错（超出838:59:59上限）
INSERT INTO time_test VALUES ('838:08:08'); -- 正常插入（范围内）

实验2：TIMESTAMP的时区敏感性

CREATE TABLE datetime_timestamp_test(a DATETIME, b TIMESTAMP);
INSERT INTO datetime_timestamp_test VALUES (NOW(), NOW()); -- 插入当前时间
SELECT * FROM datetime_timestamp_test; -- 初始查询（默认时区）

SET time_zone = '+05:00'; -- 切换时区
SELECT * FROM datetime_timestamp_test; -- 结果显示：TIMESTAMP值随时区变化，DATETIME不变

实验3：自动维护创建/更新时间

通过DEFAULT与ON UPDATE实现时间自动记录：

CREATE TABLE default_time_test(
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  name VARCHAR(10),
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（插入时自动记录）',
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间（修改时自动更新）'
) ENGINE=InnoDB CHARSET=utf8mb4;

-- 插入数据（自动记录create_time）
INSERT INTO default_time_test(name) VALUES ('aa');
-- 更新数据（自动更新update_time）
UPDATE default_time_test SET name = 'bb' WHERE id = 1;

2.4.3 类型选择原则

• 仅需日期：用DATE（如生日）；仅需时间：用TIME（如每日打卡）。
• 长期时间记录（超过2038年）：用DATETIME（无2038年限制）。
• 跨时区系统：用TIMESTAMP（自动适配时区）。
• 需自动维护创建/更新时间：优先用TIMESTAMP（语法简洁）。

2.5 字符集对MySQL的影响

2.5.1 核心概念：字符集与排序规则

• 字符集：定义字符的编码方式（如UTF8MB4支持中文、emoji）。
• 排序规则：定义字符的比较规则（如是否区分大小写、重音），通常依附于字符集（如utf8mb4_general_ci不区分大小写）。

2.5.2 常见字符集对比

2.5.3 排序规则后缀

2.5.4 字符集的关键影响

1. 乱码风险：若客户端与服务端字符集不一致，会导致中文/特殊字符乱码。
2. 索引失效：关联查询中，若关联字段字符集不同，索引无法生效，导致全表扫描。

2.5.5 实践验证：乱码问题复现

-- 1. 服务端设为UTF8MB4，创建表
SET NAMES utf8mb4;
CREATE TABLE charset_test1(aaa VARCHAR(10)) CHARSET = utf8mb4;

-- 2. 客户端切换为GBK，插入中文
SET NAMES gbk;
INSERT INTO charset_test1(aaa) VALUES ('中文'); -- 直接报错（严格模式下）

-- 3. 临时关闭严格模式后插入
select @@sql_mode; -- 查看sql_mode
SET SESSION sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- 修改sql_mode，去掉其中的STRICT_TRANS_TABLES
INSERT INTO charset_test1(aaa) VALUES ('中文'); -- 提示警告，但插入成功
show warnings; -- 查看warning内容，提示我们写入的数据不对

-- 4. 查询结果（乱码）
SELECT * FROM charset_test1; -- 显示乱码（编码不匹配导致）

2.6 MySQL字符集的配置方法

2.6.1 查看字符集信息

• 查看所有可用字符集：

  SHOW CHARACTER SET;
  

• 查看指定字符集的排序规则（以UTF8MB4为例）：

  SHOW COLLATION WHERE Charset = 'utf8mb4';
  

2.6.2 多级别字符集配置

1. 服务器级（全局默认）

• 查看当前配置：

  SHOW VARIABLES LIKE 'character_set_server'; -- 字符集
  SHOW VARIABLES LIKE 'collation_server'; -- 排序规则
  

• 配置方式：
  • 启动参数：mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci
  • 配置文件（my.conf）：

    # 打开并编辑配置文件
    vim /data/mysql/conf/my.conf
    [mysqld]
    character_set_server = utf8mb4
    collation_server = utf8mb4_0900_ai_ci
    

  • 动态修改（需权限）：

    SET GLOBAL character_set_server = utf8mb4;
    SET GLOBAL collation_server = utf8mb4_0900_ai_ci;
    

2. 数据库级（覆盖服务器级）

创建数据库时指定：

CREATE DATABASE latin_test CHARACTER SET latin1 COLLATE latin1_swedish_ci;

查看数据库字符集：

# 方式一
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA 
WHERE SCHEMA_NAME = 'latin_test';

# 方式二
use latin_test;
SELECT @@character_set_database, @@collation_database;

# 方式三
show create database latin_test;

3. 表级（覆盖数据库级）

创建表时指定：

CREATE TABLE test1(a VARCHAR(10)) 
ENGINE=InnoDB 
CHARSET=utf8mb4 
COLLATE=utf8mb4_0900_ai_ci;

查看当前表的字符集：

show create table test1;

修改表字符集：

ALTER TABLE test1 CHARSET = latin1 COLLATE = latin1_danish_ci;

4. 字段级（覆盖表级）

创建表时指定字段字符集：

CREATE TABLE column_character_test(
  name VARCHAR(10) CHARSET latin1 COLLATE latin1_danish_ci
);

查看字段的字符集：

show create table column_character_test;

修改字段字符集：

ALTER TABLE column_character_test 
MODIFY name VARCHAR(10) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

2.7 字符集使用建议

1. 显式声明：建库、建表时必须明确指定字符集（如CHARSET=utf8mb4），避免依赖默认配置导致迁移乱码。
2. 全局统一：确保客户端（如Java/Python代码）、服务端、导入文件的字符集一致（优先UTF8MB4）。
3. 优先UTF8MB4：无特殊限制时，默认用UTF8MB4（支持所有字符，兼容未来需求）。

2.8 案例：商品表设计实践

2.8.1 需求说明

设计product_info表，存储商品信息，需包含名称、价格、生产日期等字段，字符集为UTF8MB4。

2.8.2 建表语句及设计思路

CREATE TABLE product_info(
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键（自增ID）',
  prod_name VARCHAR(50) NOT NULL COMMENT '商品名称（可变长度，非空）',
  prod_price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '商品价格（定点数，精确到分）',
  prod_date TIMESTAMP DEFAULT NULL COMMENT '生产日期（含时分秒）',
  prod_manufacturer VARCHAR(50) DEFAULT NULL COMMENT '厂商名称（可变长度）',
  prod_sort ENUM('洗漱日化','生鲜水果','休闲百货','酒水调料','其他') DEFAULT NULL COMMENT '商品分类（单选枚举）',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间（自动生成）',
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间（自动更新）',
  KEY idx_prod_name(prod_name) -- 名称索引，优化查询
) ENGINE = InnoDB 
  CHARSET=utf8mb4 
  COLLATE=utf8mb4_0900_ai_ci 
  COMMENT '商品信息表';

设计思路：

• 价格用DECIMAL(10,2)：确保金额精度无误差。
• 分类用ENUM：限制可选值，减少错误数据。
• 时间字段用TIMESTAMP：自动维护创建/更新时间，简化代码。

2.9 章末总结

• 字段类型选择需平衡精度、性能、存储（如金融用DECIMAL，整数用最小够用类型）。
• 时间类型需根据范围、时区需求选择（如跨时区用TIMESTAMP）。
• 字符集是乱码与性能的关键影响因素，需全局统一为UTF8MB4，避免隐性问题。

2.10 章末作业

设计student_info表，存储学生信息，包含学号（10000-20000）、成绩（0-100，保留1位小数）、生日等字段，要求自动记录创建/更新时间，字符集为UTF8MB4。

参考建表语句：

CREATE TABLE student_info(
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  stu_name VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
  stu_num INT NOT NULL CHECK(stu_num BETWEEN 10000 AND 20000) COMMENT '学生学号（范围限制）',
  stu_score DECIMAL(4,1) DEFAULT 0 CHECK(stu_score BETWEEN 0 AND 100) COMMENT '学生成绩（精度与范围限制）',
  stu_idcard VARCHAR(20) DEFAULT NULL COMMENT '学生身份证号码',
  stu_birthday DATE DEFAULT NULL COMMENT '学生生日（仅日期）',
  stu_parent_phone VARCHAR(15) DEFAULT NULL COMMENT '家长联系方式',
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录修改时间'
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_0900_ai_ci 
  COMMENT='学生信息表';