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

来源：这里教程网时间：2026-02-28 20:40:18 作者：

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 <table_name></table_name>
，确认引擎是
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_20240520 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();

实际操作中，最易被忽略的是字符集与排序规则的隐式继承——新建表时若没指定