做SQL入门系列做到第五篇前面几篇聊的是SELECT查询、WHERE过滤、ORDER BY排序都是“读”数据的操作。今天这一篇换到“写”的方向聊SQL表操作里的三件套定义、插入、复制。说人话就是建表、往表里塞数据、给表做副本。我为什么把这三件事单独拎出来写因为在我接触过的项目里查询相关的坑其实好排查反而建表和复制表这种“看起来太简单”的操作最容易给后续留下大隐患。有人用FLOAT存金额导致对账不平有人复制完才发现新表没有主键有人批量插入几十万行写到一半遇到唯一键冲突整批回滚还是部分成功都搞不清楚。这些问题都不属于语法不会而是对表操作的底层行为缺乏预期。这篇就按定义、插入、复制三个主线的顺序来写每一部分都会给出可执行的写法、背后的原理以及我实际踩过或见过别人踩的坑。适合刚学完SELECT、准备开始写库表的朋友也适合写了一段时间、想系统梳理一遍DDL和DML边界的人。1. 建表不只是一句CREATE TABLE字段类型、约束与设计习惯1.1 字段类型偷懒一时爽填坑火葬场建表的本质是提前告诉数据库“这一列将来会存放什么形状的数据”。字段类型选错了后面所有查询、索引、应用层代码都得跟着遭殃。拿最常见的订单金额举例。我看到很多初学者喜欢用FLOAT或DOUBLE存价格理由是“省事能存小数”。但浮点数的二进制表示天生有精度误差0.1 0.2 算出来可能是 0.30000000000000004。存个10.01元读出来变成10.009999999999998单笔差一丁点没事成千上万笔汇总对账的时候差出来的几分钱就能让你排查一整天。正确的做法是用DECIMAL也就是定点数比如DECIMAL(10, 2)意思是总位数10位小数点后保留2位银行、电商、财务系统都这么干。再看整型。订单表的ID、用户ID这类字段通常用BIGINT而不是INT。很多人觉得INT已经够大但INT的上限是21亿多现在很多业务表轻轻松松就过亿行加上删除、归档、合并带来的ID消耗过几年撞到上限再改表代价巨大。还见过把手机号存成BIGINT的结果遇到一个以0开头的号码前导零直接被丢掉数据就废了。手机号、身份证号、订单号这种“看起来是数字但不需要参与算术运算”的字段一律用VARCHAR。日期时间字段也是重灾区。MySQL里有DATETIME和TIMESTAMPDATETIME存的是字面时间不随时区变化TIMESTAMP存的是UTC时间戳会随会话时区转换。业务上要记录“用户下单这个时刻”用DATETIME更稳妥如果要记录“这条记录最近一次修改时间”配合DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP用TIMESTAMP反而方便。但如果你用的是PostgreSQL又有timestamp with time zone和timestamp without time zone之分命名完全不同换数据库时千万别凭肌肉记忆。1.2 约束让数据库替你把关别什么都靠应用层建表时写不写约束直接决定数据质量。约束不是性能负担而是数据库替你守门的保安。最常见的四类约束主键PRIMARY KEY、非空NOT NULL、唯一UNIQUE、默认值DEFAULT。外键FOREIGN KEY在互联网大厂的高并发场景里经常被禁用因为强一致性约束在高写入压力下会拖慢性能但在中小项目、内部管理系统里外键依然是非常值得用的数据一致性保障。你不需要一上来就全用但主键、非空、唯一这三样几乎是业务表标配。举个例子订单表里order_no订单号一定要加 UNIQUE 约束。没有唯一约束你应用层里就算先查再插也挡不住两个并发请求同时插入相同订单号——这个竞态条件恰恰是线上最容易出问题的。加了唯一约束第二个插入直接报错数据库立刻帮你拦下一笔脏数据。同理user_id和created_at这类核心业务字段必须 NOT NULL否则后面写统计报表的时候你会被一堆 NULL 折腾得生不如死。我说句实话所有“先不建约束等后面再补”的想法最后基本都补不回来。因为存量数据早就混进了各种脏值你再想加 UNIQUE 约束数据库会拒绝执行——它会先扫一遍历史数据发现重复值就报错。数据质量差的表连加约束的资格都没有。1.3 命名规范和结构注释写给三个月后的自己命名这块没有绝对标准但要形成自己的统一习惯。我的个人习惯是表名单数字段名用小写加下划线snake_case索引名以idx_开头唯一键名以uk_开头。这样在团队协作里光看名字就能判断一个约束是索引还是唯一键排查问题快很多。表名和字段名一旦定下来后续改名的成本极高因为代码、报表、BI、数据同步工具全都引用着它。所以建表之前先在纸上把字段清单列一遍想清楚再上终端比什么都重要。另外建表语句里一定要写 COMMENT。MySQL的COMMENT虽然只是元数据但半年后你回过头看一张status字段如果不注释说明“0待支付 1已支付 2已取消 3退款中”你只能靠猜。PostgreSQL则推荐用COMMENT ON COLUMN语句来单独加注释习惯不太一样。2. 插入数据INSERT的语法细节、批量操作与默认值边界2.1 最基本的INSERT写法列名清单是必需品先看标准写法INSERT INTO orders (order_no, user_id, status, total_amount, created_at) VALUES (SO20240101001, 1001, 0, 199.00, NOW());列名清单写出来好处是字段顺序和表定义解耦。哪怕表结构新增了字段只要这条INSERT没用到它就仍然能正常执行。还有一种简写INSERT INTO orders VALUES (...)。这种写法要求 VALUES 里给出的字段必须和表结构定义时的顺序完全一致一个不多一个不少。如果表结构中间加过字段这条SQL立刻报废。我见过不止一次开发环境跑得好好的脚本推到生产环境就报错原因就是生产表结构多了一列。所以我的建议很直接除非是临时在命令行里验证数据任何正式脚本都要把列名清单写全这属于职业习惯问题。2.2 多行插入效率差距可以到几十倍INSERT的VALUES子句支持一次写多行INSERT INTO orders (order_no, user_id, status, total_amount) VALUES (SO20240101002, 1002, 0, 299.00), (SO20240101003, 1003, 1, 1599.00), (SO20240101004, 1004, 2, 59.90);为什么推荐多行插入因为每条INSERT都伴随一次SQL解析、权限检查、事务日志写入。1000条单行INSERT要来回1000次网络交互一条多行INSERT只来一次。在MySQL的InnoDB引擎下批量插入的速度可以提升几十倍。但多行插入也有边界一次插入的行数太多SQL语句体积会超过数据库的max_allowed_packet限制MySQL默认一般4MB或64MB直接报错。稳妥的做法是分批比如每500行或每1000行一组循环提交。PostgreSQL还有COPY、MySQL有LOAD DATA INFILE这些是真正在压大批量数据时用的手段普通业务INSERT就够。2.3 NULL、DEFAULT与自增主键省略列的真正含义插入时可以省略非必填列。如果设计表时给某列定义了DEFAULT省略它时会自动填默认值如果没定义默认值且列允许NULL就会存NULL如果列是NOT NULL又没默认值省略它直接报错。这一点值得展开。很多人以为“省略列 插入NULL”其实不完全正确——省略的列走的是“默认值表达式”而显式写NULL走的是“赋值NULL”。例如created_at定义了DEFAULT CURRENT_TIMESTAMP省略它就是拿当前时间显式写NULL如果列允许NULL就会存进一个NULL。两种写法结果完全不同别混。自增主键AUTO_INCREMENT / IDENTITY / SERIAL在插入时通常不写或者显式写NULL让它自动生成。但有个坑如果一次事务里插入了10行然后事务回滚了自增ID不会回退。也就是说ID序列中间会出现空洞这是正常现象不要开发一个“所有ID必须连续”的逻辑否则线上会把你折磨疯。MySQL的AUTO_INCREMENT、SQL Server的IDENTITY、PostgreSQL的SERIAL都有这个特性。2.4 插入操作的事务边界成功了一半算怎么回事默认情况下MySQL的autocommit是开启的一条INSERT就是隐式事务要么全成要么全败。但批量操作时如果你手动用BEGIN开启显式事务插入1000行遇到第500行违反唯一约束在InnoDB默认的REPEATABLE READ隔离级别下整个事务执行到出错语句就终止了但前面499行不会自动回滚——如果你不主动ROLLBACK它们就留在库里了。这恰恰是很多人犯迷糊的地方。写批量脚本时我的习惯是这样START TRANSACTION; INSERT INTO orders (order_no, user_id, status, total_amount) VALUES (SO20240101005, 1005, 0, 199.00), (SO20240101006, 1006, 1, 299.00); -- 手动检查影响行数确认无误再提交 COMMIT;如果插入中间报错我会直接ROLLBACK把整个事务撤销而不是让它处于一个“写了一半”的暧昧状态。宁可脚本失败重跑也不能让库里出现只有一半数据、还无法追溯的情况。3. 复制表看似简单CTAS、LIKE与INSERT INTO SELECT的差异与坑3.1 为什么需要复制表临时表、备份与环境隔离复制表是实际开发中使用频率极高的操作。最常见的场景有三个备份表线上数据在搞大改动之前先复制一张orders_bak_20240115保留现状出问题能快速回退。报表临时表跑一次性统计不希望影响线上OLTP负载复制一份数据到临时库再查。环境隔离测试环境没有生产环境的表结构快速复制一张结构一致的表来联调。这些需求听起来简单但不同数据库的复制语法差异很大而且“复制表”这三个字在不同数据库里的含义根本不一样。3.2 一张表看清三种主流复制方式的区别我这里把最常用的三种方式放一起对比方式语法复制结构复制数据复制约束/索引/默认值CTASCREATE TABLE new AS SELECT * FROM old普通列定义是不复制LIKE结构复制CREATE TABLE new LIKE old完整结构否MySQL复制PostgreSQL需加INCLUDING选项SELECT INTOSELECT * INTO new FROM oldSQL Server普通列定义是不复制看这张表就能明白为什么说复制表容易出问题CTAS和SELECT INTO只复制“列形状”不复制主键、索引、默认值、自增属性连外键关系也通通不带。很多新手复制完才发现新表连主键都没有后续想加唯一约束却因为数据重复失败最后只能重建表。PostgreSQL的LIKE比较特殊它提供了INCLUDING ALL这种选项可以把索引、约束、默认值都带上CREATE TABLE orders_backup (LIKE orders INCLUDING ALL);这句话会把结构、默认值、约束、索引全复制过来但数据仍然没有你再单独插入。MySQL的CREATE TABLE new LIKE old行为更贴近“完整结构复制”约束和自增属性都会带上但不带数据也不带外键MySQL官方文档说明外键定义会被保留在表结构里但通常建议二次确认。3.3 既要结构又要数据时别偷懒最稳妥的“结构数据”复制方式是先建结构再插数据-- 第一步复制完整结构MySQL写法 CREATE TABLE orders_backup LIKE orders; -- 第二步把数据搬进去 INSERT INTO orders_backup SELECT * FROM orders;这一步拆开对我来说几乎成了习惯。因为每一步都可以单独验证——先DESC orders_backup看看结构约束对不对再插入数据前确认目标表主键、自增、默认值是否齐全。结构没问题了再灌数据出错的概率会小很多。直接用CREATE TABLE orders_backup AS SELECT * FROM orders的确一行就搞定但当你发现新表缺少索引导致查询慢到爆炸的时候又得重新建表导入数据等于白干一遍。3.4 复制表后的三大验证动作复制表之后先别急着写业务代码做三个快速验证验证结构DESC orders_backup;或者SHOW CREATE TABLE orders_backup;确认主键、索引、默认值都在。验证数据量SELECT COUNT(*) FROM orders; SELECT COUNT(*) FROM orders_backup;两个数要对得上。验证边界数据专门查一下原来表里有NULL值的行、极端长度的字符串、最大最小值确认复制后没有变形。为什么第三点重要因为不同引擎对不同数据类型的处理细节不一致。比如某字段是DECIMAL(10,2)CTAS复制后列类型一般没问题但如果是ENUM这类数据库方言特性很重的类型跨库CTAS可能直接变成VARCHAR甚至报错。跨数据库复制表时我还会用information_schema.COLUMNS把两边字段类型拉出来对照一遍省得靠肉眼。4. 实战演练从零建一张订单表再复制出报表临时表4.1 需求场景一张能扛住线上环境的订单表假设我在做一个电商项目现在需要一张订单主表。要求订单号唯一、用户ID必须有、金额精确到分、状态有默认值、记录创建和更新时间。建表语句如下CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 主键ID, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT NOT NULL COMMENT 下单用户ID, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态 0待支付 1已支付 2已取消 3退款中, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单总金额, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) COMMENT订单主表;这条语句包含了主键、唯一键、普通索引、非空、默认值、自增。注意updated_at在MySQL里用了ON UPDATE CURRENT_TIMESTAMP这样每次行数据被修改这个字段会自动刷新省去应用层手写更新时间。4.2 灌入测试数据验证约束是否真的在干活INSERT INTO orders (order_no, user_id, status, total_amount) VALUES (SO20240101001, 1001, 0, 199.00), (SO20240101002, 1002, 1, 299.00), (SO20240101003, 1003, 2, 59.90);插入后立刻验证约束再插一次同样的订单号试试效果INSERT INTO orders (order_no, user_id, status, total_amount) VALUES (SO20240101001, 1004, 0, 99.00);执行后数据库会报Duplicate entry SO20240101001 for key orders.uk_order_no。看到这个报错你应该高兴说明唯一约束确实挡住了重复订单号。再试试非空约束把user_id去掉插入INSERT INTO orders (order_no, status, total_amount) VALUES (SO20240101004, 0, 88.00);数据库会报Field user_id doesnt have a default value。这两次报错都是正常的因为约束正在按预期工作。你在真实项目里调试新表时就通过这种故意插入非法数据的方式给自己建立对表结构的信心。4.3 复制表给线上数据做一套备份加临时报表现在要在不改动线上表的前提下做三张表第一张结构完整、数据齐全的备份表CREATE TABLE orders_backup LIKE orders; INSERT INTO orders_backup SELECT * FROM orders;第二张只带结构、用来测试新功能的空表CREATE TABLE orders_dev LIKE orders;这个orders_dev没有数据结构约束跟线上完全一致测试代码随便改不会污染线上。第三张只需要部分字段、报表统计专用的汇总表CREATE TABLE orders_report AS SELECT user_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent FROM orders GROUP BY user_id;这张表是CTAS出来的注意它没有主键、没有索引它是给BI报表临时用的低并发表可以接受。但如果你拿这种表当业务表用那问题就大了。4.4 复制后的体检我每次必做的三查在这个实战里我会执行以下确认-- 1. 查看orders_backup完整结构 SHOW CREATE TABLE orders_backup; -- 2. 对比orders和orders_backup的行数 SELECT (SELECT COUNT(*) FROM orders) AS src_cnt, (SELECT COUNT(*) FROM orders_backup) AS bak_cnt; -- 3. 查看orders_report的字段类型和索引情况 DESC orders_report;如果orders_backup的SHOW CREATE TABLE结果里没有主键或索引就要停下来检查是不是复制方式选错了。CTAS结构复制出现的常见问题基本都能在这一步暴露出来。5. 建表与复制操作的实用建议和避坑清单5.1 建表之前先在纸面上排一遍字段不要一上来就打开客户端写SQL。拿张纸或表格工具把业务对象的字段一个一个列出来每个字段想清楚三件事类型、是否为空、默认值。想不清楚的字段先不放宁缺毋滥。因为加字段虽然方便但改字段类型和删字段会非常痛苦尤其当数据量过亿的时候一条ALTER TABLE能把数据库卡到不可用。5.2 复制表之前先问自己三个问题我要复制结构还是数据还是两者都要新表放在哪个库权限和表名是否合规复制完之后这张表会长期用还是用完就删这三个问题的答案直接决定复制方式。一次性临时表就CTAS用完直接DROP要长期用得规范就先LIKE再INSERT只要空结构做开发直接LIKE不带数据。很多人踩坑都是因为没想清楚第三个问题把一次性临时表用成了长期业务表后续维护成本全砸自己头上。5.3 生产环境操作给自己留好回退路径在线上的库里做结构调整我从来不会直接对原表动手。标准操作是先建一张新表在新表上做改动然后通过改名切换最后保留下旧表一段时间。复制表就是这套流程的第一步因此复制之后不要急着删原表。曾经见过有人急着清理空间复制完直接DROP原表结果发现新表少了几张依赖表的关联关系整个晚上的数据全得重跑。备份表多放几天磁盘空间貴一点但心理踏实。5.4 各数据库方言差异备忘最后列一个我自己的备忘录希望对新人有帮助MySQLCREATE TABLE ... LIKE复制结构保留索引和自增CTAS不保留索引和约束。PostgreSQLCREATE TABLE ... (LIKE ... INCLUDING ALL)才能带全套约束索引CTAS同样不带约束。SQL ServerSELECT * INTO new FROM old只能复制结构加数据不带约束索引只建结构用SELECT TOP 0 * INTO new FROM old。OracleCREATE TABLE new AS SELECT * FROM old跟其他家CTAS行为一致不带约束索引。跨数据库项目里千万别用一套语法走天下。就算在同一家公司MySQL和PostgreSQL两套体系并存也很常见。写迁移脚本时我一般都会先在一个小测试机上验证语法再推到目标环境执行。5.5 最后分享一个排查表结构的常用查询很多初学者不知道去哪看表结构。除了DESC和SHOW CREATE TABLE还可以直接查系统元数据SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_database AND TABLE_NAME orders ORDER BY ORDINAL_POSITION;这条SQL在任何支持information_schema的数据库上都能跑MySQL、PostgreSQL、SQL Server都认。当你需要自动对比两张表的结构差异时把两边查出来做差集比对比肉眼盯着几十个字段看靠谱得多。建表、插数据、复制表这三件事难吗语法都不难半天就能学会。真正的门槛在于你清不清楚每个操作背后会带上什么、不会带上什么以及操作完成后有没有验证的习惯。我写了几年SQL最大的体会就是把每次建表都当成一次小工程来做字段类型多花一分钟确认复制表以后多花一分钟体检后面省下来的时间无数组。
SQL表操作三件套：建表、插入与复制，避坑指南
做SQL入门系列做到第五篇前面几篇聊的是SELECT查询、WHERE过滤、ORDER BY排序都是“读”数据的操作。今天这一篇换到“写”的方向聊SQL表操作里的三件套定义、插入、复制。说人话就是建表、往表里塞数据、给表做副本。我为什么把这三件事单独拎出来写因为在我接触过的项目里查询相关的坑其实好排查反而建表和复制表这种“看起来太简单”的操作最容易给后续留下大隐患。有人用FLOAT存金额导致对账不平有人复制完才发现新表没有主键有人批量插入几十万行写到一半遇到唯一键冲突整批回滚还是部分成功都搞不清楚。这些问题都不属于语法不会而是对表操作的底层行为缺乏预期。这篇就按定义、插入、复制三个主线的顺序来写每一部分都会给出可执行的写法、背后的原理以及我实际踩过或见过别人踩的坑。适合刚学完SELECT、准备开始写库表的朋友也适合写了一段时间、想系统梳理一遍DDL和DML边界的人。1. 建表不只是一句CREATE TABLE字段类型、约束与设计习惯1.1 字段类型偷懒一时爽填坑火葬场建表的本质是提前告诉数据库“这一列将来会存放什么形状的数据”。字段类型选错了后面所有查询、索引、应用层代码都得跟着遭殃。拿最常见的订单金额举例。我看到很多初学者喜欢用FLOAT或DOUBLE存价格理由是“省事能存小数”。但浮点数的二进制表示天生有精度误差0.1 0.2 算出来可能是 0.30000000000000004。存个10.01元读出来变成10.009999999999998单笔差一丁点没事成千上万笔汇总对账的时候差出来的几分钱就能让你排查一整天。正确的做法是用DECIMAL也就是定点数比如DECIMAL(10, 2)意思是总位数10位小数点后保留2位银行、电商、财务系统都这么干。再看整型。订单表的ID、用户ID这类字段通常用BIGINT而不是INT。很多人觉得INT已经够大但INT的上限是21亿多现在很多业务表轻轻松松就过亿行加上删除、归档、合并带来的ID消耗过几年撞到上限再改表代价巨大。还见过把手机号存成BIGINT的结果遇到一个以0开头的号码前导零直接被丢掉数据就废了。手机号、身份证号、订单号这种“看起来是数字但不需要参与算术运算”的字段一律用VARCHAR。日期时间字段也是重灾区。MySQL里有DATETIME和TIMESTAMPDATETIME存的是字面时间不随时区变化TIMESTAMP存的是UTC时间戳会随会话时区转换。业务上要记录“用户下单这个时刻”用DATETIME更稳妥如果要记录“这条记录最近一次修改时间”配合DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP用TIMESTAMP反而方便。但如果你用的是PostgreSQL又有timestamp with time zone和timestamp without time zone之分命名完全不同换数据库时千万别凭肌肉记忆。1.2 约束让数据库替你把关别什么都靠应用层建表时写不写约束直接决定数据质量。约束不是性能负担而是数据库替你守门的保安。最常见的四类约束主键PRIMARY KEY、非空NOT NULL、唯一UNIQUE、默认值DEFAULT。外键FOREIGN KEY在互联网大厂的高并发场景里经常被禁用因为强一致性约束在高写入压力下会拖慢性能但在中小项目、内部管理系统里外键依然是非常值得用的数据一致性保障。你不需要一上来就全用但主键、非空、唯一这三样几乎是业务表标配。举个例子订单表里order_no订单号一定要加 UNIQUE 约束。没有唯一约束你应用层里就算先查再插也挡不住两个并发请求同时插入相同订单号——这个竞态条件恰恰是线上最容易出问题的。加了唯一约束第二个插入直接报错数据库立刻帮你拦下一笔脏数据。同理user_id和created_at这类核心业务字段必须 NOT NULL否则后面写统计报表的时候你会被一堆 NULL 折腾得生不如死。我说句实话所有“先不建约束等后面再补”的想法最后基本都补不回来。因为存量数据早就混进了各种脏值你再想加 UNIQUE 约束数据库会拒绝执行——它会先扫一遍历史数据发现重复值就报错。数据质量差的表连加约束的资格都没有。1.3 命名规范和结构注释写给三个月后的自己命名这块没有绝对标准但要形成自己的统一习惯。我的个人习惯是表名单数字段名用小写加下划线snake_case索引名以idx_开头唯一键名以uk_开头。这样在团队协作里光看名字就能判断一个约束是索引还是唯一键排查问题快很多。表名和字段名一旦定下来后续改名的成本极高因为代码、报表、BI、数据同步工具全都引用着它。所以建表之前先在纸上把字段清单列一遍想清楚再上终端比什么都重要。另外建表语句里一定要写 COMMENT。MySQL的COMMENT虽然只是元数据但半年后你回过头看一张status字段如果不注释说明“0待支付 1已支付 2已取消 3退款中”你只能靠猜。PostgreSQL则推荐用COMMENT ON COLUMN语句来单独加注释习惯不太一样。2. 插入数据INSERT的语法细节、批量操作与默认值边界2.1 最基本的INSERT写法列名清单是必需品先看标准写法INSERT INTO orders (order_no, user_id, status, total_amount, created_at) VALUES (SO20240101001, 1001, 0, 199.00, NOW());列名清单写出来好处是字段顺序和表定义解耦。哪怕表结构新增了字段只要这条INSERT没用到它就仍然能正常执行。还有一种简写INSERT INTO orders VALUES (...)。这种写法要求 VALUES 里给出的字段必须和表结构定义时的顺序完全一致一个不多一个不少。如果表结构中间加过字段这条SQL立刻报废。我见过不止一次开发环境跑得好好的脚本推到生产环境就报错原因就是生产表结构多了一列。所以我的建议很直接除非是临时在命令行里验证数据任何正式脚本都要把列名清单写全这属于职业习惯问题。2.2 多行插入效率差距可以到几十倍INSERT的VALUES子句支持一次写多行INSERT INTO orders (order_no, user_id, status, total_amount) VALUES (SO20240101002, 1002, 0, 299.00), (SO20240101003, 1003, 1, 1599.00), (SO20240101004, 1004, 2, 59.90);为什么推荐多行插入因为每条INSERT都伴随一次SQL解析、权限检查、事务日志写入。1000条单行INSERT要来回1000次网络交互一条多行INSERT只来一次。在MySQL的InnoDB引擎下批量插入的速度可以提升几十倍。但多行插入也有边界一次插入的行数太多SQL语句体积会超过数据库的max_allowed_packet限制MySQL默认一般4MB或64MB直接报错。稳妥的做法是分批比如每500行或每1000行一组循环提交。PostgreSQL还有COPY、MySQL有LOAD DATA INFILE这些是真正在压大批量数据时用的手段普通业务INSERT就够。2.3 NULL、DEFAULT与自增主键省略列的真正含义插入时可以省略非必填列。如果设计表时给某列定义了DEFAULT省略它时会自动填默认值如果没定义默认值且列允许NULL就会存NULL如果列是NOT NULL又没默认值省略它直接报错。这一点值得展开。很多人以为“省略列 插入NULL”其实不完全正确——省略的列走的是“默认值表达式”而显式写NULL走的是“赋值NULL”。例如created_at定义了DEFAULT CURRENT_TIMESTAMP省略它就是拿当前时间显式写NULL如果列允许NULL就会存进一个NULL。两种写法结果完全不同别混。自增主键AUTO_INCREMENT / IDENTITY / SERIAL在插入时通常不写或者显式写NULL让它自动生成。但有个坑如果一次事务里插入了10行然后事务回滚了自增ID不会回退。也就是说ID序列中间会出现空洞这是正常现象不要开发一个“所有ID必须连续”的逻辑否则线上会把你折磨疯。MySQL的AUTO_INCREMENT、SQL Server的IDENTITY、PostgreSQL的SERIAL都有这个特性。2.4 插入操作的事务边界成功了一半算怎么回事默认情况下MySQL的autocommit是开启的一条INSERT就是隐式事务要么全成要么全败。但批量操作时如果你手动用BEGIN开启显式事务插入1000行遇到第500行违反唯一约束在InnoDB默认的REPEATABLE READ隔离级别下整个事务执行到出错语句就终止了但前面499行不会自动回滚——如果你不主动ROLLBACK它们就留在库里了。这恰恰是很多人犯迷糊的地方。写批量脚本时我的习惯是这样START TRANSACTION; INSERT INTO orders (order_no, user_id, status, total_amount) VALUES (SO20240101005, 1005, 0, 199.00), (SO20240101006, 1006, 1, 299.00); -- 手动检查影响行数确认无误再提交 COMMIT;如果插入中间报错我会直接ROLLBACK把整个事务撤销而不是让它处于一个“写了一半”的暧昧状态。宁可脚本失败重跑也不能让库里出现只有一半数据、还无法追溯的情况。3. 复制表看似简单CTAS、LIKE与INSERT INTO SELECT的差异与坑3.1 为什么需要复制表临时表、备份与环境隔离复制表是实际开发中使用频率极高的操作。最常见的场景有三个备份表线上数据在搞大改动之前先复制一张orders_bak_20240115保留现状出问题能快速回退。报表临时表跑一次性统计不希望影响线上OLTP负载复制一份数据到临时库再查。环境隔离测试环境没有生产环境的表结构快速复制一张结构一致的表来联调。这些需求听起来简单但不同数据库的复制语法差异很大而且“复制表”这三个字在不同数据库里的含义根本不一样。3.2 一张表看清三种主流复制方式的区别我这里把最常用的三种方式放一起对比方式语法复制结构复制数据复制约束/索引/默认值CTASCREATE TABLE new AS SELECT * FROM old普通列定义是不复制LIKE结构复制CREATE TABLE new LIKE old完整结构否MySQL复制PostgreSQL需加INCLUDING选项SELECT INTOSELECT * INTO new FROM oldSQL Server普通列定义是不复制看这张表就能明白为什么说复制表容易出问题CTAS和SELECT INTO只复制“列形状”不复制主键、索引、默认值、自增属性连外键关系也通通不带。很多新手复制完才发现新表连主键都没有后续想加唯一约束却因为数据重复失败最后只能重建表。PostgreSQL的LIKE比较特殊它提供了INCLUDING ALL这种选项可以把索引、约束、默认值都带上CREATE TABLE orders_backup (LIKE orders INCLUDING ALL);这句话会把结构、默认值、约束、索引全复制过来但数据仍然没有你再单独插入。MySQL的CREATE TABLE new LIKE old行为更贴近“完整结构复制”约束和自增属性都会带上但不带数据也不带外键MySQL官方文档说明外键定义会被保留在表结构里但通常建议二次确认。3.3 既要结构又要数据时别偷懒最稳妥的“结构数据”复制方式是先建结构再插数据-- 第一步复制完整结构MySQL写法 CREATE TABLE orders_backup LIKE orders; -- 第二步把数据搬进去 INSERT INTO orders_backup SELECT * FROM orders;这一步拆开对我来说几乎成了习惯。因为每一步都可以单独验证——先DESC orders_backup看看结构约束对不对再插入数据前确认目标表主键、自增、默认值是否齐全。结构没问题了再灌数据出错的概率会小很多。直接用CREATE TABLE orders_backup AS SELECT * FROM orders的确一行就搞定但当你发现新表缺少索引导致查询慢到爆炸的时候又得重新建表导入数据等于白干一遍。3.4 复制表后的三大验证动作复制表之后先别急着写业务代码做三个快速验证验证结构DESC orders_backup;或者SHOW CREATE TABLE orders_backup;确认主键、索引、默认值都在。验证数据量SELECT COUNT(*) FROM orders; SELECT COUNT(*) FROM orders_backup;两个数要对得上。验证边界数据专门查一下原来表里有NULL值的行、极端长度的字符串、最大最小值确认复制后没有变形。为什么第三点重要因为不同引擎对不同数据类型的处理细节不一致。比如某字段是DECIMAL(10,2)CTAS复制后列类型一般没问题但如果是ENUM这类数据库方言特性很重的类型跨库CTAS可能直接变成VARCHAR甚至报错。跨数据库复制表时我还会用information_schema.COLUMNS把两边字段类型拉出来对照一遍省得靠肉眼。4. 实战演练从零建一张订单表再复制出报表临时表4.1 需求场景一张能扛住线上环境的订单表假设我在做一个电商项目现在需要一张订单主表。要求订单号唯一、用户ID必须有、金额精确到分、状态有默认值、记录创建和更新时间。建表语句如下CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 主键ID, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT NOT NULL COMMENT 下单用户ID, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态 0待支付 1已支付 2已取消 3退款中, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单总金额, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) COMMENT订单主表;这条语句包含了主键、唯一键、普通索引、非空、默认值、自增。注意updated_at在MySQL里用了ON UPDATE CURRENT_TIMESTAMP这样每次行数据被修改这个字段会自动刷新省去应用层手写更新时间。4.2 灌入测试数据验证约束是否真的在干活INSERT INTO orders (order_no, user_id, status, total_amount) VALUES (SO20240101001, 1001, 0, 199.00), (SO20240101002, 1002, 1, 299.00), (SO20240101003, 1003, 2, 59.90);插入后立刻验证约束再插一次同样的订单号试试效果INSERT INTO orders (order_no, user_id, status, total_amount) VALUES (SO20240101001, 1004, 0, 99.00);执行后数据库会报Duplicate entry SO20240101001 for key orders.uk_order_no。看到这个报错你应该高兴说明唯一约束确实挡住了重复订单号。再试试非空约束把user_id去掉插入INSERT INTO orders (order_no, status, total_amount) VALUES (SO20240101004, 0, 88.00);数据库会报Field user_id doesnt have a default value。这两次报错都是正常的因为约束正在按预期工作。你在真实项目里调试新表时就通过这种故意插入非法数据的方式给自己建立对表结构的信心。4.3 复制表给线上数据做一套备份加临时报表现在要在不改动线上表的前提下做三张表第一张结构完整、数据齐全的备份表CREATE TABLE orders_backup LIKE orders; INSERT INTO orders_backup SELECT * FROM orders;第二张只带结构、用来测试新功能的空表CREATE TABLE orders_dev LIKE orders;这个orders_dev没有数据结构约束跟线上完全一致测试代码随便改不会污染线上。第三张只需要部分字段、报表统计专用的汇总表CREATE TABLE orders_report AS SELECT user_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent FROM orders GROUP BY user_id;这张表是CTAS出来的注意它没有主键、没有索引它是给BI报表临时用的低并发表可以接受。但如果你拿这种表当业务表用那问题就大了。4.4 复制后的体检我每次必做的三查在这个实战里我会执行以下确认-- 1. 查看orders_backup完整结构 SHOW CREATE TABLE orders_backup; -- 2. 对比orders和orders_backup的行数 SELECT (SELECT COUNT(*) FROM orders) AS src_cnt, (SELECT COUNT(*) FROM orders_backup) AS bak_cnt; -- 3. 查看orders_report的字段类型和索引情况 DESC orders_report;如果orders_backup的SHOW CREATE TABLE结果里没有主键或索引就要停下来检查是不是复制方式选错了。CTAS结构复制出现的常见问题基本都能在这一步暴露出来。5. 建表与复制操作的实用建议和避坑清单5.1 建表之前先在纸面上排一遍字段不要一上来就打开客户端写SQL。拿张纸或表格工具把业务对象的字段一个一个列出来每个字段想清楚三件事类型、是否为空、默认值。想不清楚的字段先不放宁缺毋滥。因为加字段虽然方便但改字段类型和删字段会非常痛苦尤其当数据量过亿的时候一条ALTER TABLE能把数据库卡到不可用。5.2 复制表之前先问自己三个问题我要复制结构还是数据还是两者都要新表放在哪个库权限和表名是否合规复制完之后这张表会长期用还是用完就删这三个问题的答案直接决定复制方式。一次性临时表就CTAS用完直接DROP要长期用得规范就先LIKE再INSERT只要空结构做开发直接LIKE不带数据。很多人踩坑都是因为没想清楚第三个问题把一次性临时表用成了长期业务表后续维护成本全砸自己头上。5.3 生产环境操作给自己留好回退路径在线上的库里做结构调整我从来不会直接对原表动手。标准操作是先建一张新表在新表上做改动然后通过改名切换最后保留下旧表一段时间。复制表就是这套流程的第一步因此复制之后不要急着删原表。曾经见过有人急着清理空间复制完直接DROP原表结果发现新表少了几张依赖表的关联关系整个晚上的数据全得重跑。备份表多放几天磁盘空间貴一点但心理踏实。5.4 各数据库方言差异备忘最后列一个我自己的备忘录希望对新人有帮助MySQLCREATE TABLE ... LIKE复制结构保留索引和自增CTAS不保留索引和约束。PostgreSQLCREATE TABLE ... (LIKE ... INCLUDING ALL)才能带全套约束索引CTAS同样不带约束。SQL ServerSELECT * INTO new FROM old只能复制结构加数据不带约束索引只建结构用SELECT TOP 0 * INTO new FROM old。OracleCREATE TABLE new AS SELECT * FROM old跟其他家CTAS行为一致不带约束索引。跨数据库项目里千万别用一套语法走天下。就算在同一家公司MySQL和PostgreSQL两套体系并存也很常见。写迁移脚本时我一般都会先在一个小测试机上验证语法再推到目标环境执行。5.5 最后分享一个排查表结构的常用查询很多初学者不知道去哪看表结构。除了DESC和SHOW CREATE TABLE还可以直接查系统元数据SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_database AND TABLE_NAME orders ORDER BY ORDINAL_POSITION;这条SQL在任何支持information_schema的数据库上都能跑MySQL、PostgreSQL、SQL Server都认。当你需要自动对比两张表的结构差异时把两边查出来做差集比对比肉眼盯着几十个字段看靠谱得多。建表、插数据、复制表这三件事难吗语法都不难半天就能学会。真正的门槛在于你清不清楚每个操作背后会带上什么、不会带上什么以及操作完成后有没有验证的习惯。我写了几年SQL最大的体会就是把每次建表都当成一次小工程来做字段类型多花一分钟确认复制表以后多花一分钟体检后面省下来的时间无数组。
