MYSQL数据类型和建表

·薯条大王

于 2025-03-29 22:40:21 发布

阅读量577

点赞数 24

本文链接：https://blog.youkuaiyun.com/2302_79462679/article/details/146715654

版权

数据库专栏收录该内容

14 篇文章

订阅专栏

一、数据类型

1. 数值类型

整数型

无符号与填充：
TINYINT UNSIGNED 范围变为 0~255，INT(5) ZEROFILL 显示为 00123（不足位数补零）。
自增属性：
AUTO_INCREMENT 仅用于整数类型（如主键字段），默认从1开始递增。
DECIMAL 与浮点型的区别：
DECIMAL 是精确类型，适合财务计算（如金额），而 FLOAT/DOUBLE 是近似类型，可能因精度丢失导致计算误差，FLOAT 精度约7位小数，DOUBLE 精度约15位小数。

2. 字符串类型

CHAR vs VARCHAR：

CHAR(10) 存储 "abc" 时占用10字符（尾部补空格），检索时自动去除空格。
VARCHAR(10) 存储 "abc" 时占用3字符 + 1字节长度信息。
性能对比：
CHAR 查询效率更高（定长结构），VARCHAR 节省存储空间。
ENUM 和 SET：
ENUM('男','女')：仅允许选择一个值，存储时用整数代替字符串，节省空间。SET('读书','音乐')：允许选择多个值（如 '读书,音乐'），用位掩码存储。

3. 日期与时间类型

TIMESTAMP 特性：

存储为UTC时间，检索时根据会话时区转换（如 SET time_zone = '+8:00' 显示北京时间）。

范围：1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。

自动初始化/更新：

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

二、建表与约束

1. 外键约束的深度说明

语法细节：

FOREIGN KEY (当前表字段) REFERENCES 目标表(目标字段) 
  [ON DELETE CASCADE]  -- 级联删除
  [ON UPDATE SET NULL] -- 级联置空

注意事项：

外键字段和参考字段的数据类型必须完全一致。

仅 InnoDB 引擎支持外键，MyISAM 引擎无效。

大量外键可能影响写入性能。

2. 索引优化

常见索引类型：

普通索引：INDEX idx_name (name)

唯一索引：UNIQUE INDEX idx_email (email)

全文索引：FULLTEXT INDEX idx_content (content)（适用于 TEXT 字段的模糊搜索）

索引选择原则：

频繁作为查询条件的字段（如 WHERE、JOIN、ORDER BY）。

避免对更新频繁的字段建索引。

3. 表结构修改示例

添加字段：

ALTER TABLE employee ADD COLUMN phone VARCHAR(15) AFTER name;

修改字段类型：


ALTER TABLE employee MODIFY COLUMN age SMALLINT UNSIGNED;

删除字段：

ALTER TABLE employee DROP COLUMN phone;

添加主键：

ALTER TABLE employee ADD PRIMARY KEY (id);

三、高级实践建议

字符集与排序规则

推荐使用 utf8mb4 字符集（支持 emoji 和生僻字），排序规则 utf8mb4_unicode_ci。

建表时指定：

CREATE TABLE ... DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

存储引擎选择

InnoDB：支持事务、行级锁、外键，适合高并发写入场景。

MyISAM：仅表级锁，不支持事务，适合读多写少的静态表（如日志表）。

避免 NULL 的设计
可为 NULL 的字段会占用额外存储空间，且查询优化复杂。
- 建议设置默认值：
```
address VARCHAR(100) DEFAULT '',  
is_deleted TINYINT DEFAULT 0  
```

命名规范

表名、字段名使用小写字母 + 下划线（如 order_detail）。

主键字段可命名为 id，外键字段为 关联表名_id（如 user_id）。

四、完整建表示例

CREATE TABLE orders (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(20) NOT NULL UNIQUE COMMENT '订单号',
    user_id INT UNSIGNED NOT NULL,
    amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    status ENUM('pending', 'paid', 'shipped') NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';