mysql数据库的表创建与数据修改操作

日期：2026-01-14 00:00 / 作者：P粉602998670

MySQL建表需显式声明主键、字段类型及约束，推荐使用BIGINT UNSIGNED主键、VARCHAR(255)、DATETIME、utf8mb4_unicode_ci；UPDATE必须带WHERE；ALTER大表慎用；优先选INSERT...ON DUPLICATE KEY UPDATE而非REPLACE INTO。

CREATE TABLE 语句必须显式声明主键和字段类型

MySQL 8.0+ 默认启用 STRICT_TRANS_TABLES 模式，不写主键、用 INT 不写长度、漏掉 NOT NULL 约束都可能让建表失败或埋下隐患。尤其注意：没有显式定义主键时，InnoDB 会悄悄创建隐藏的 ROW_ID，但无法被 SQL 引用，也不支持外键关联。

INT 类型建议写成 INT UNSIGNED（如自增 ID），避免负值干扰
字符串字段优先用 VARCHAR(255) 而非 TEXT，后者不参与内存排序、不能建全文索引（除非用 fulltext 显式声明）
时间字段统一用 DATETIME（而非 TIMESTAMP），后者受时区影响且范围窄（1970–2038）
建表后立刻执行 SHOW CREATE TABLE ，确认引擎是 InnoDB、字符集是 utf8mb4

CREATE TABLE users (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE 必须带 WHERE 条件，否则整表覆写

这是线上事故最高发操作之一。UPDATE users SET status = 'inactive' 这种语句没加 WHERE，会把全表所有记录状态改成 inactive，且无法靠 binlog 直接回滚（因为 binlog 是语句级或行级，但恢复需人工判断）。

执行前先用 SELECT COUNT(*) FROM users WHERE ... 验证条件匹配行数
开发环境开启 SQL_SAFE_UPDATES=1，此时无 WHERE 或 WHERE 不含索引字段会报错
修改前备份快照：CREATE TABLE users_bak_20250520 AS SELECT * FROM users WHERE 1
涉及多字段更新时，避免用子查询更新自身表（MySQL 报错 You can't specify target table for update in FROM clause），改用 JOIN 或临时表

ALTER TABLE 修改字段要警惕锁表与复制延迟

MySQL 5.6+ 对部分 ALTER TABLE 操作支持 ALGORITHM=INPLACE，但不是所有修改都免锁。比如给大表（千万级以上）加 NOT NULL 约束、改字段类型（VARCHAR(100) → VARCHAR(500)）、加唯一索引，仍会触发表拷贝，阻塞写入数分钟甚至小时。

用 SHOW PROCESSLIST 观察 State 是否为 copy to tmp table，说明正在锁表
生产环境优先使用 pt-online-schema-change（Percona Toolkit）做在线 DDL
修改默认值（ALTER TABLE t ALTER COLUMN c SET DEFAULT 'x'）不锁表，但只影响新插入行；已有数据不会自动填充
删除列（DROP COLUMN）在 InnoDB 中仍是重建表，慎用

INSERT ON DUPLICATE KEY UPDATE 和 REPLACE INTO 的行为差异

两者都能处理重复键冲突，但机制完全不同：REPLACE INTO 是「删 + 插」，会触发 DELETE 和 INSERT 两个事件，自增 ID 会跳号，外键关联记录可能被级联删除；而 INSERT ... ON DUPLICATE

KEY UPDATE 是原地更新，更安全可控。

只有当表有 PRIMARY KEY 或 UNIQUE 索引时，冲突检测才生效
ON DUPLICATE KEY UPDATE 中的 VALUES(col) 表示本次 INSERT 尝试插入的值，不是当前行旧值
如果想更新多个字段，用逗号分隔：UPDATE name = VALUES(name), updated_at = NOW()
不要用 REPLACE INTO 做“存在则更新”，它本质是删除动作，不适合有外键依赖或触发器的场景

INSERT INTO users (id, name, email) 
VALUES (123, 'Alice', 'alice@example.com') 
ON DUPLICATE KEY UPDATE 
  name = VALUES(name), 
  email = VALUES(email), 
  updated_at = NOW();

实际操作中，最易被忽略的是字符集与排序规则的隐式继承——新建表时若没指定 COLLATE，会沿用数据库默认值，而不同 collation（如 utf8mb4_general_ci vs utf8mb4_unicode_ci）会导致 ORDER BY 结果不一致、唯一索引判定偏差。建表语句里那行 COLLATE=utf8mb4_unicode_ci 不是装饰，是必要声明。