SQL表操作核心：建表、插入数据与复制表实战全解析

写 SQL 入门系列到这一篇前面几章基本都在围着 SELECT 转圈。不少读者后台问过我同样的问题表是怎么来的结构是谁定义的为什么我往表里插数据老是报错还有线上表怎么快速复制一份到测试库这些问题其实都属于同一个大主题——表操作。这篇我就把三板斧一次讲清楚定义表CREATE TABLE、插入数据INSERT、复制表结构复制、数据复制都算。这是从“只会查”迈向“真正写库”的分水岭也是后面理解事务、索引优化、表分区这些高级话题的地基。不管你在用 MySQL、SQL Server 还是 PostgreSQL底层逻辑完全一样差异只在写法细节我会把这些差异点也一并标出来。1. 先把思路捋清楚表操作到底在解决什么问题1.1 表就是 SQL 世界的“容器”容器没选好后面全是坑我一直喜欢把数据库表比作搬家用的收纳箱。CREATE TABLE 是去商店选箱子决定箱子的形状、大小、隔层——对应到数据库就是有哪些字段、每个字段存什么类型、哪些字段不能为空、哪一列是唯一标识。INSERT 是往箱子里放东西对应到数据库就是把真实业务数据写进表。复制表则更像你看到邻居家的收纳方案特别好干脆照着人家箱子的尺寸、隔层设计甚至把里面的东西一起搬来一套。这三件事的顺序不能乱。你得先有箱子才能放东西先有表结构才能插数据复制也得先确定要复制的源表结构长什么样。很多新手一上来就 INSERT报错“Unknown column”时才回头去查表结构这就是流程反了。先想清楚定义再动手写数据后面几乎不会遇到这类低级问题。1.2 设计表之前先回答四个问题很多初学者拿到需求就直接开敲CREATE TABLE字段名随手起类型凭感觉选。等你真跑起业务会发现字段不够用、类型存不下、过滤条件查不动改表结构比刚开始设计多花好几倍时间。我一般会先逼自己回答四个问题答完再写 DDL这张表记录的是哪个业务对象是用户、订单、商品还是日志对象决定了表名和核心字段。哪些字段是业务必需的哪些是可以后面再加的核心字段第一次建表就放进扩展字段先留注释不要一上来堆 30 列。哪些字段会频繁变化比如用户积分、订单状态这类字段要单独考虑索引和更新频率不能和固定信息搅在一起。主要查询场景是什么是查“某个用户最近 10 笔订单”还是查“某天全站的订单量”查询方式决定你要不要加索引、要不要冗余字段。举例来说要建一个订单表我会先列出订单号、用户 ID、商品 ID、数量、单价、总金额、订单状态、创建时间、更新时间这些核心字段再考虑是否需要支付时间、收货地址、备注等信息。等你把字段想清楚建表语句基本就八九不离十了。2. 建表前的“地基”数据类型与约束2.1 数据类型选型这一步选错后面全乱数据类型是表定义里最基础也最容易忽视的一环。很多人图省事不管什么字段都上 VARCHAR结果日期不能比较、金额出现精度丢失、状态字段大小写混乱。我建议你对常用类型心里有数哪怕记不住关键字也要知道“这个类型解决什么问题”。先看一张主流数据库对照表用途MySQLSQL ServerPostgreSQL说明整数一般INTINTINTEGER用户 ID、数量等常规整数整数大范围BIGINTBIGINTBIGINT雪花 ID、流水号别用 INT 存会溢出小数精确DECIMAL(10,2)DECIMAL(10,2)NUMERIC(10,2)金额必须用它不能用 FLOAT/DOUBLE定长字符串CHAR(n)CHAR(n)CHAR(n)长度基本不变如国家代码、性别变长字符串VARCHAR(n)VARCHAR(n)VARCHAR(n)用户名、备注等n 是字符数不是字节数大文本TEXT / LONGTEXTNVARCHAR(MAX)TEXT长文章不要拿它当普通字段滥用日期时间DATETIME / TIMESTAMPDATETIME2TIMESTAMP订单时间、创建时间这里有三条我踩过坑的经验第一金额类字段永远用 DECIMAL别用 FLOAT。FLOAT 是近似存储0.1 0.2 这种简单算账都会出精度问题做财务系统更是红线。第二VARCHAR(255) 的 255 是字符数不是字节数。中文一个字符占 3 个字节UTF-8 下如果你用 VARCHAR(50) 存 50 个汉字完全没有问题但如果你按字节理解很容易把长度设得过大或过小。过小会导致插入中文报“Data too long”。第三TIMESTAMP 有时区概念DATETIME 没有。跨时区业务、全球部署的数据库优先用 TIMESTAMP或 PostgreSQL 的 TIMESTAMPTZ否则同一条记录在不同时区的人看到的时间不一致。2.2 约束是表规则的“说明书”但也不是越多越好约束是 CREATE TABLE 里除了字段类型之外最重要的语法块。它解决的是“数据库层面怎么保证数据合法”的问题不依赖任何应用代码。主键PRIMARY KEY唯一标识一行最核心的约束。我强烈建议用自增整数或雪花 ID不要用业务字段做主键。比如“身份证号”看起来唯一但用户可能修改、可能没填、可能录入错误一旦做主键后面想改都难。自增列简单、性能好、永不重复。外键FOREIGN KEY表示表和表的关联关系。学习阶段一定要理解外键的作用它能防止你插入一个不存在的用户 ID保证引用完整性。真实生产环境里很多团队为了写入性能会去掉外键把校验放在应用层但那是分布式拆库后的妥协不是新手该学的偷懒方式。唯一约束UNIQUE保证某列的取值不重复比如用户名、手机号。它和主键的区别是一张表只能有一个主键但可以有多个唯一约束而且唯一约束允许 NULLMySQL 里多个 NULL 算不重复。非空约束NOT NULL业务必需字段必须加。常见反例是建表时不设 NOT NULL结果插入数据时漏字段全变 NULL统计时还要到处IS NULL。默认值DEFAULT没传值时的兜底。比如created_at DATETIME DEFAULT CURRENT_TIMESTAMP插入时不用管这列数据库自动填当前时间。CHECK 约束MySQL 8.0.16 之后才真正生效SQL Server、PostgreSQL 一直支持。比如年龄字段 CHECKage 0从源头挡住不合法的数据写进去。注意约束不是越多越好。每个约束都有校验成本写入越频繁约束过多对性能影响越大。我见过一张日志表加了 6 个外键结果每秒写几千条时 MySQL CPU 直接飙红。正确的做法是核心业务表该加的约束一定加高频写入的无状态流水表只保留主键和必要非空把外键这类重校验放给应用层。3. 表定义实操一条龙手把手写出可用的 CREATE TABLE3.1 一个完整建表语句是怎么写出来的我拿最典型的用户表举例先写 MySQL 版本CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID自增主键, username VARCHAR(50) NOT NULL COMMENT 用户名登录用, password_hash CHAR(64) NOT NULL COMMENT 密码哈希值绝不存明文, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱允许为空, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0禁用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户基础表;逐行解释几个关键点INT UNSIGNED无符号整数ID 不会为负数等于把可用范围翻了一倍。AUTO_INCREMENT自增列每次插入自动加 1省得手动生成 ID。CHAR(64)存密码哈希SHA-256 的十六进制串固定 64 位定长类型性能优于 VARCHAR。DEFAULT CURRENT_TIMESTAMP创建时间由数据库托底不依赖应用传参。ON UPDATE CURRENT_TIMESTAMP这行数据被 UPDATE 时自动刷新更新时间超级实用。很多新手的updated_at永远不变就是建表时少了这句。UNIQUE KEY uk_username用户名唯一同时自带索引登录按用户名查时能走索引。再看 SQL Server 版本思路一样语法细节有差异CREATE TABLE [dbo].[User] ( [Id] INT IDENTITY(1,1) NOT NULL, [Username] NVARCHAR(50) NOT NULL, [PasswordHash] CHAR(64) NOT NULL, [Email] NVARCHAR(100) NULL, [Status] TINYINT NOT NULL CONSTRAINT DF_User_Status DEFAULT (1), [CreatedAt] DATETIME2 NOT NULL CONSTRAINT DF_User_CreatedAt DEFAULT (SYSDATETIME()), [UpdatedAt] DATETIME2 NOT NULL CONSTRAINT DF_User_UpdatedAt DEFAULT (SYSDATETIME()), CONSTRAINT PK_User PRIMARY KEY ([Id]), CONSTRAINT UK_User_Username UNIQUE ([Username]) );SQL Server 用的是IDENTITY(1,1)而不是AUTO_INCREMENT自增的起点和步长可以在括号里直接控制这点比 MySQL 灵活。NVARCHAR是 Unicode 变长字符串SQL Server 里建议优先用它存中文避免乱码。约束都用CONSTRAINT 约束名显式命名方便后面删改和维护。3.2 建表时最容易踩的 5 个坑第一表名或字段名撞了保留字。user、order、group在部分数据库里是保留字。MySQL 里可以用反引号包起来SQL Server 用方括号但最省事的办法是起名时避开比如sys_user、t_order。第二字符集没指定。MySQL 5.7 及以前的默认字符集是 latin1直接建中文表容易乱码。建表语句里明确写DEFAULT CHARSETutf8mb4宁可多打几个字别指望全局配置兜底。第三VARCHAR 长度理解错。前面说过括号里的数字是字符数不是字节。一个 VARCHAR(10) 在 utf8mb4 下最多能存 10 个汉字不是 10 个字节。设计长度时要按“最多有多少字符”算不要按“多少字节”算。第四忘记加字段注释。COMMENT这东西看似不起眼三个月后回来看表结构没有注释的字段你根本想不起来它是干嘛的。团队协作时字段注释就是最低成本的文档。SQL Server 没有 COMMENT 语法但可以用扩展属性或者建表后用sp_addextendedproperty加说明麻烦归麻烦还是要养成习惯。第五表名大小写问题。MySQL 在 Linux 上默认区分表名大小写在 Windows 上默认不区分这就导致开发环境好好的 SQL部署到 Linux 服务器上就报“Table doesnt exist”。解决办法是统一用小写表名多个单词用下划线分隔并且lower_case_table_names1这种配置要团队统一。4. 插入数据INSERT 的三种主流姿势4.1 单行插入与多行批量插入效率差了一个量级表定义好之后第一件事是往里灌数据。最基础的单行插入长这样INSERT INTO user (username, password_hash, email, status) VALUES (zhangsan, a1b2c3..., zhangsanexample.com, 1);注意我没有写id和created_at它们分别由自增和默认值处理。新手最容易犯的错是把所有字段都往 INSERT 里塞包括自增主键和自动时间结果要么报错要么插进去的时间和预期不符。多行插入是 MySQL 和 PostgreSQL 非常好用的一个特性INSERT INTO user (username, password_hash, email, status) VALUES (zhangsan, a1b2c3..., zhangsanexample.com, 1), (lisi, d4e5f6..., lisiexample.com, 1), (wangwu, g7h8i9..., NULL, 0);一批 1000 行和一行一行插 1000 次效率差别非常明显。前者只发起一次网络请求后者要发起 1000 次每次都包含 SQL 解析、权限校验、事务开销。我自己实测过本地 MySQL 插入 10 万行单条循环插入要几十秒多行批量插入只要 1 秒左右。SQL Server 从 2008 开始也支持这种VALUES多行写法但要注意批量大小我建议不要超过 1000 行一批超过就拆不然报文太大反而卡。还有个小技巧如果你插入的数据来自一个已经存在的表完全不用手动敲 VALUESINSERT INTO user (username, password_hash, email, status) SELECT real_name, md5_password, email, 1 FROM temp_user WHERE status 1;这是 INSERT 的第三种主流姿势也是后面讲表复制的前置技能。每次做这种操作我先 SELECT 验证一下列数和类型再改成 INSERT能省下大量调试时间。4.2 插入时常见的 4 个“坑”主键冲突是最常见的。重复插入同一 ID 时MySQL 报Duplicate entry 1 for key PRIMARY。如果你希望“存在就更新不存在才插入”MySQL 可以用ON DUPLICATE KEY UPDATESQL Server 可以用MERGE后面实战再用吧入门阶段先理解主键冲突的本质是“唯一标识重复”。第二个坑是外键校验失败。插入引用表数据时外键列的值必须在被引用表里存在否则直接报外键约束错误。这是个保护机制提示你业务数据有依赖问题不要为了不报错就去删外键。第三个坑是中文乱码。表现是插入后查出来是???或一堆乱码。原因通常是三个层级的字符集不一致客户端连接字符集、数据库/表字符集、字段字符集。你用 Navicat 看到乱码先检查连接的字符集设置再把表字符集统一为 utf8mb4基本能解决九成问题。第四个坑是字符串里的引号。往 SQL 里拼业务文本时如果文本里带单引号不处理就会语法错误。这不是 SQL 问题是拼字符串的姿势不对正确做法是用参数化查询让数据库驱动帮你处理转义。顺便说一句这也是防止 SQL 注入的根本手段后面安全部分细讲。5. 表复制实操从只复制结构到完整克隆复制表是开发里频率很高的操作。做测试要造数据、开新功能要备份旧表、跨环境要迁移结构全都能用上。但要分清楚复制表有两个维度是只要结构还是结构数据一起要是复制到同库还是跨库跨服务器。5.1 只要结构不要数据CREATE TABLE ... LIKEMySQL 提供了最直接的语法CREATE TABLE user_bak LIKE user;这条语句会完整复制user的字段定义、默认值、自增属性、索引甚至 COMMENT 都会带过来。唯一不复制的是外键这是 MySQL 官方文档明确写的外键不会复制到新表。所以复制完检查一下是否需要手动补外键。SQL Server 没有 LIKE 这种语法。它的常见做法是SELECT TOP 0 * INTO user_bak FROM [user];SELECT ... INTO创建新表并把查询结果插入TOP 0表示不取任何行结果就是只复制结构。但它有个坑它不会复制主键、默认值、索引、约束。新表只是个“长得像”的空表你得手动ALTER TABLE补主键、补约束。PostgreSQL 对应的是CREATE TABLE user_bak (LIKE user INCLUDING ALL);INCLUDING ALL会把默认值、约束、索引统统带上是最省事的一种。5.2 结构与数据一起复制CREATE TABLE AS SELECT这是我最常用的方式。MySQL 和 PostgreSQL 都是CREATE TABLE user_bak AS SELECT * FROM user;SQL Server 用SELECT * INTO user_bak FROM user;这条命令执行完一张包含全部数据的新表就诞生了。效率非常高因为它是数据库内部操作不经过应用层一条条读再一条条写。复制百万级数据的表也就几秒到几十秒的事。但必须强调一个重点CTAS 和 SELECT INTO 复制出来的表本质上只是“数据裸结构”主键、自增、索引、默认值、外键这些统统不复制。举个例子源表id是自增主键复制出来的新表id只是一个普通 INT 列不能自增。你需要手动补救ALTER TABLE user_bak MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id); ALTER TABLE user_bak ADD UNIQUE KEY uk_username (username);所以我的习惯是先复制一张表但不着急用先把约束、索引补齐了再对外提供服务。否则应用层还不知道怎么回事就往非自增的主键列插 NULL直接报错。5.3 跨库、跨服务器复制怎么搞同一个 MySQL 实例里跨库复制最简单CREATE TABLE other_db.user_bak AS SELECT * FROM current_db.user;只要账号有权限直接带库名前缀就行。跨服务器复制我常用的手段是mysqldump导出再导入mysqldump -uroot -p --single-transaction --default-character-setutf8mb4 test_db user user.sql mysql -uroot -p -h目标IP test_db user.sql--single-transaction是为了 InnoDB 导数据时保持一致快照避免导出过程中数据变化导致不一致。SQL Server 的跨服务器方案要么用 SSMS 的导出数据向导要么配置链接服务器后直接用SELECT INTO但链接服务器跨网络拉数据时性能一般大数据量还是走导出文件更稳。无论哪种方式导出导入后我都一定会做一件事对比源表和目标表的行数。数据量差一行都不行这是检验复制是否完整的底线。可以写一句快速对比SELECT COUNT(*) FROM 源库.user; SELECT COUNT(*) FROM 目标库.user_bak;5.4 大表复制的性能优化与一致性复制千万级甚至亿级的大表再简单的CREATE TABLE AS SELECT也不能随便跑。两个风险一是执行时间长会拖垮源库性能二是中途失败留下一张残缺的半成品表。我的做法是分批处理。按自增主键范围切片CREATE TABLE user_part1 AS SELECT * FROM user WHERE id BETWEEN 1 AND 1000000; CREATE TABLE user_part2 AS SELECT * FROM user WHERE id BETWEEN 1000001 AND 2000000; -- 最后把各分区合并或直接按分区使用第二个技巧是“先禁索引复制完再建”。如果目标表是手工CREATE TABLE建好的可以先不建索引数据插完后再统一加索引比边插边维护索引快得多。这就好比你往书架里塞书如果每塞一本都要按字母顺序重新排列肯定慢先把书胡乱堆满再花一次时间统一排序反而快。一致性方面如果线上有业务还在写源表单独跑一条CREATE TABLE AS SELECT无法保证业务一致性。更稳的做法是先复制到临时表确认无误后使用原子切换RENAME TABLE user TO user_old, user_bak TO user;MySQL 的RENAME TABLE是原子操作要么全部成功要么全部失败不会出现切换一半的中间状态。线上结构变更我基本都靠这招。6. 实操中的问题排查与安全提醒6.1 高频报错速查表写表操作这个阶段新手高频遇到的错误就那么几个我整理成一个速查表报错信息里出现类似字样直接对照解决方案。错误类型典型报错原因解决方案字段不存在Unknown column xxx in field listINSERT 的列名和表结构不一致DESC 表名查看真实字段名主键冲突Duplicate entry 1 for key PRIMARY插入的主键已存在改业务主键或用 ON DUPLICATE KEY UPDATE非空约束违反Column username cannot be nullNOT NULL 字段传了 NULL检查插入数据补齐必填字段外键失败Cannot add or update a child row外键列的值在被引用表不存在先查被引用表确认数据存在字符集乱码插入后查出来是 ???客户端/表/字段字符集不一致统一 utf8mb4检查连接字符集数据过长Data too long for column email插入内容超过 VARCHAR 长度加长长度或截断数据6.2 慢 SQL 优化的入门三板斧热搜词里一直有“慢sql优化”前面讲的INSERT ... SELECT和CREATE TABLE AS SELECT在数据量大时也可能慢。当你发现复制操作卡到怀疑人生第一反应不是加服务器内存而是用 EXPLAIN 看执行计划。EXPLAIN SELECT * FROM user WHERE username zhangsan;看三个关键列type是不是ALL全表扫、key有没有走到索引、rows预估扫描多少行。如果type是ALL大概率是没索引或者查询条件没有包含索引前缀列。给查询条件加上匹配的索引性能往往立竿见影。复制大表如果本身就慢不要把锅全甩给 SQL。先看源表有没有大事务在写再看磁盘负载是不是打满了最后考虑分批方案。慢 SQL 排查是个系统工程但入门阶段记住“SELECT 慢先看索引INSERT 慢先看表数量和约束复制慢先看数据量级”这个方向基本不会跑偏。6.3 插入场景的 SQL 注入安全提醒表操作和安全直接相关的点在插入。很多初学者写代码时会把用户输入直接拼进 INSERT# 反面教材千万别学 cursor.execute(fINSERT INTO user (username) VALUES ({username}))如果用户在输入框里填了); DROP TABLE user; --拼出来就成了INSERT INTO user (username) VALUES (); DROP TABLE user; --)一条插入语句后面跟着一条删表语句后果可想而知。SQL 注入不只在 SELECT 里存在INSERT、UPDATE、DELETE 全都可能中招。这也就是热词里“sql注入万能密码绕过”这类攻击能被反复讨论的原因——本质都是拼接 SQL 导致语义被篡改。正确做法是参数化查询。Python 的 pymysql 写法cursor.execute( INSERT INTO user (username, password_hash) VALUES (%s, %s), (username, password_hash) )参数化后驱动会把username当作纯数据传给数据库再奇怪的输入也不会被当成 SQL 语法解析。这是所有后端语言、所有数据库驱动都支持的机制没有任何理由去拼字符串。再补充一个安全习惯密码永远不能明文存库。至少用 SHA-256 哈希更好的是加盐后用 bcrypt 这类慢哈希算法。就算表被拖库攻击者拿到一堆哈希也比直接拿到明文密码安全得多。写表操作这部分内容我自己最大的体会是建表时多想十分钟后面能省十个小时。字段类型选错、约束漏加、字符集没统一、注释没写这些坑在数据量小的时候毫无感觉等表里躺了几百万行、多个系统都在读写时每一次修改都是牵一发动全身的调整。所以我的个人习惯是每一张新表建完先回头看一遍建表语句问自己三个问题类型是不是最合适的约束有没有覆盖核心逻辑注释能不能让三个月后的我看懂这张表都点头了才开始写 INSERT。这个习惯帮我避了不少坑分享给你。