学 SQL 这事很多人一开始都盯着 SELECT 不放觉得能把查询玩出花来就是高手。真到了实际干活才发现最磨人的反而是表操作——定义表结构、往表里插入数据、把一张表复制成另一张这三件事做不扎实后面查询再溜也是空中楼阁。这篇就专门聊聊 SQL 表操作里的定义、插入与复制面向刚把 SELECT 语法吃透、正准备自己动手建库建表的入门同学也适合那些之前全靠工具点点点、想补一补原生 SQL 功底的从业者。看完你至少能知道怎么科学地定义一张表怎么把数据稳妥地插进去以及如何在不污染原表的前提下快速复制出结构相同或带数据的表。1. 建表前必须想清楚的事按惯例先说思路。我带新人的时候经常强调建表不是一个“写个 CREATE TABLE 交差”的动作而是把业务需求翻译成数据结构的过程。表定义做得好不好直接影响后面每一行查询写起来顺不顺手也影响数据库存数据稳不稳。很多人建表五分钟后面改表五小时就是这个环节没做好。1.1 为什么表定义是数据质量的源头一句话你表里放什么、不放什么、让不让为空、允不允许重复这些规则在 INSERT 的时候就已经在帮你守门了。比如你把某个字段设成了 NOT NULL应用层就算写漏了数据库也会兜底报错拦下来而不是存一个很难排查的空值进去。反过来如果表设计的时候没设主键重复数据悄悄混进来等你做统计的时候发现数字莫名翻倍那时候收拾起来就痛苦了。另一个容易忽视的点是字段类型。类型其实是数据库给你的一块“格式挡板”。用对了脏数据进不来用错了脏数据不但能进来还会在查询时给你搞出各种匪夷所思的结果。我见过有人把日期存成字符串结果做月份比较的时候全按文本排序1 月后面跟着 10 月查出来的报表完全没法看。1.2 类型、主键、NULL 三件套怎么选这里给一个最小决策清单主键优先用一个无业务含义的整型自增列当主键别拿身份证、手机号这种业务字段当主键。业务字段一旦变更关联表全要跟着改数据清洗的时候你会非常想哭。自增主键唯一的职责就是“唯一标识一行”不承载业务含义所以最稳。字段类型看准了再选。金额用 DECIMAL(18,2)别用 FLOAT浮点数的精度坑会坑到你对账对不上日期用 DATE 或 DATETIME别拿 VARCHAR 存状态位用 TINYINT 或 SMALLINT别什么都塞 VARCHAR。能用小类型就别用大类型数据量上来之后字段类型直接决定索引效率和存储成本。NULL 与默认值能设默认值就别让字段随便为 NULL。比如 create_time 默认 CURRENT_TIMESTAMPstatus 默认 0这样 INSERT 时少写好多字段也不会因为漏传值导致数据缺失。提示新手最常犯的错就是所有字段全部 VARCHAR(255) 一把梭。短期内确实省事后面做 WHERE 条件、做 JOIN、跑统计的时候性能和准确性都会很难受。等你意识到要改类型数据已经到了几十万上百万行ALTER TABLE 一次锁表锁半天代价就大了。1.3 给入门者的建表前检查清单建表之前我习惯让团队同事按下面的顺序过一遍这张表干什么用的能说出核心业务场景吗哪一列能作为唯一标识如果没有就设计一个自增 id。每一列的最小值和最大值大概是多少据此定字段类型和长度。哪些列一定不能为空哪些需要默认值平时最常按哪些列过滤、排序、关联这些列是否需要索引表会不会无限增长需不需要建分区或保留清理策略这些问题可能没法 100% 一次想清楚但至少想清楚前五条建出来的表就已经能打七十分了。剩下的三十分在迭代中慢慢用 ALTER TABLE 加工也来得及。2. 用 CREATE TABLE 定义一张表工具选型我这里不展开吹MySQL、SQL Server、PostgreSQL 都行。核心要明白一点不管用哪个数据库CREATE TABLE 的核心语法是差不多的差异主要在自增怎么写、复制表怎么写这些方言细节上。尤其是近几年的 SQL Server 2019、2022 和 MySQL 8.0基本语法一致性很高学会一套换库也就是查一下文档的区别。2.1 最基础的建表语法拆解先看一个通用的例子CREATE TABLE user_info ( id INT PRIMARY KEY, user_name VARCHAR(64) NOT NULL, age INT, email VARCHAR(128), created_at DATETIME DEFAULT CURRENT_TIMESTAMP );逐行拆一下id 那行INT 表示整数主键PRIMARY KEY 声明主键约束保证这一列不重复且不为空。这里先故意不写自增方便说明基础概念。user_name 那行VARCHAR(64) 是可变长字符串括号里的 64 是最大字符数NOT NULL 表示这列必填。用户名一定得有所以设成 NOT NULL。age、email 那行允许为空适合“可填可不填”的信息但注意日后统计时要专门处理 NULL。created_at 那行DEFAULT CURRENT_TIMESTAMP 意思是“如果不手动传值数据库自动填当前时间”很多表都有创建时间放这里正合适。2.2 自增、约束、默认值这些细节自增主键在不同数据库里写法不一样MySQLid INT AUTO_INCREMENT PRIMARY KEYSQL Serverid INT IDENTITY(1,1) PRIMARY KEYPostgreSQLid SERIAL PRIMARY KEY数据量大的话更推荐id BIGSERIAL PRIMARY KEY你可能觉得这些写法就是背就完了其实背后的思路是统一的主键的生成不应该依赖应用层数据库自己递增最省心。并发高的场景下应用层自己生成主键要么撞车要么要搞雪花算法麻烦得很。如果还要实现“用户名不能重复”直接在表上加唯一约束CREATE TABLE user_info ( id INT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(64) NOT NULL UNIQUE, email VARCHAR(128) UNIQUE );UNIQUE 和“插入前查一遍再插入”相比优势是约束在数据库层生效并发情况下也不容易出现两条相同数据同时插入成功。这一点在高并发注册场景尤为明显业务逻辑里先查后插总会有时间窗口而数据库的唯一索引是原子校验。2.3 一个完整例子用户表我实际工作中很常用的一张用户表大概是下面这样CREATE TABLE user_info ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 用户ID, user_name VARCHAR(64) NOT NULL UNIQUE COMMENT 登录名, nickname VARCHAR(64) DEFAULT COMMENT 昵称, age TINYINT UNSIGNED COMMENT 年龄, email VARCHAR(128) DEFAULT COMMENT 邮箱, phone VARCHAR(20) DEFAULT COMMENT 手机号, status TINYINT NOT NULL DEFAULT 1 COMMENT 1正常 0禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, KEY idx_status_create (status, create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这里比第一版多了几个细节COMMENT 写中文备注方便后来的人维护TINYINT UNSIGNED 存年龄其实年龄一般也不会超过 255status 字段带默认值 1插入时不传就是正常状态update_time 用 ON UPDATE CURRENT_TIMESTAMP 自动更新省得应用层每次手动维护更新时间最后加了一个联合索引 idx_status_create覆盖“按状态创建时间筛选”的常见后台列表查询。心得COMMENT 一定要写。表字段少的时候你觉得无所谓等过了一年你看着一个 status 字段完全想不起 0 和 1 什么意思除非翻代码。写备注是投入产出比最高的好习惯。3. INSERT INTO把数据写进表建完表不塞数据等于白建。INSERT 看起来特别简单但实际开发里翻车最多的反而就是插入环节字段顺序、字符集、类型隐式转换、主键冲突随便一个都能让批量任务当场挂掉。3.1 三种常用的插入写法第一种是单行插入INSERT INTO user_info (user_name, age, email) VALUES (张三, 28, zhangsanexample.com);字段列表写清楚VALUES 里的值按顺序和字段一一对应。我强烈建议永远都写字段列表哪怕你要插入所有列也写上。不写字段列表直接 VALUES 全列一旦字段顺序变了或者新加了列代码就悄悄出错而且报错还算好最怕的是没报错、数据插歪了。第二种是多行插入INSERT INTO user_info (user_name, age, email) VALUES (李四, 21, lisiexample.com), (王五, NULL, NULL), (赵六, 35, zhaoliuexample.com);一条 SQL 插多行能减少多次网络往返批量导数据时效率高很多。但要注意一次别插太多比如一次性 VALUES 一万条SQL 文本会很巨大容易超过数据库缓冲区上限一般建议分批每批 500 到 1000 条比较舒服。第三种是把查询结果直接插进去INSERT INTO user_info (user_name, age, email) SELECT user_name, age, email FROM temp_user WHERE status 1;这种写法在数据迁移、临时表加工、报表中间表构建时特别常用。它的核心优势是不用先把数据捞到应用层再塞回去所有操作都发生在数据库内部速度跨几个量级。3.2 插入时容易踩的坑我按踩坑频率排个序字段顺序不匹配。VALUES 的顺序和字段列表必须一一对应少一个、多一个、反一个都会出错。NOT NULL 字段没传值。如果表里必填字段没有默认值INSERT 漏掉会直接报错错误信息说的是 Column cannot be null。自增主键手动指定。如果你手动插入 id后续自增计数器可能和已有数据错位过一阵子再插入就会撞主键。要用显式 id尽量用数据库的 AUTO_INCREMENT。字符串引号问题。SQL 字符串用单引号别用双引号因为双引号在不少数据库里被当成标识符列名/表名来用。一不小心写错报错还算轻更麻烦的是语义被改。中文乱码。客户端字符集和表字符集不一致时插入中文可能变成问号。新版本的 MySQL、SQL Server 都默认 utf8mb4 了但如果你的库是祖传的 latin1、gbk就得多确认连接串里的 charset 配置。提示批量插入数据量很大的时候可以先把表上的索引临时去掉插完再重建速度能快好几倍。我导历史数据时经常这么干几百万行数据从十几分钟压到两三分钟。前提是你能接受插入过程中相关查询变慢以及中途失败要重来。3.3 大批量导入LOAD DATA 和 BULK INSERT如果数据量上了几十万上百万行用 INSERT 一条条拼 SQL 已经不现实了。这时候 MySQL 可以用 LOAD DATA INFILESQL Server 可以用 BULK INSERT都是直接把文本文件或 CSV 批量塞进表里速度比逐条 INSERT 快一个量级以上。-- MySQL 导入 CSV LOAD DATA INFILE /tmp/user.csv INTO TABLE user_info FIELDS TERMINATED BY , IGNORE 1 LINES (user_name, age, email); -- SQL Server 导入 CSV BULK INSERT user_info FROM C:\data\user.csv WITH ( FIELDTERMINATOR ,, FIRSTROW 2 );这类工具适合日志数据、历史数据搬迁、离线数据初始化这些场景。用的时候注意两点一是文件路径要数据库服务能访问到不是随便指个客户端本地路径就行二是导入前最好先用小文件试跑一遍确认字段映射和字符集没问题再上全量。4. 复制表三分钟建一张一样的表复制表算是面试里高频、工作里也高频的需求。比如你想在测试环境复刻生产环境的一张表或者想给现有表做个备份再或者想把一张宽表拆成几张窄表、给报表组临时拉一张落地表都离不开复制。4.1 不同数据库的复制语法差异复制表要分清层次只复制结构、复制结构加数据、只复制数据到已存在的表。不同数据库方言差很多我整理了一张常用对照表目标MySQLSQL ServerPostgreSQL只复制结构CREATE TABLE t2 LIKE t1SELECT * INTO t2 FROM t1 WHERE 10CREATE TABLE t2 (LIKE t1)复制结构数据CREATE TABLE t2 AS SELECT * FROM t1SELECT * INTO t2 FROM t1CREATE TABLE t2 AS SELECT * FROM t1只复制数据INSERT INTO t2 SELECT * FROM t1INSERT INTO t2 SELECT * FROM t1INSERT INTO t2 SELECT * FROM t1这张表建议直接收藏。我用得最多的是 MySQL 的 CREATE TABLE ... LIKE 和 CREATE TABLE ... AS SELECT 这两个组合-- 复制结构不含数据 CREATE TABLE user_info_bak LIKE user_info; -- 复制结构加数据 CREATE TABLE user_info_bak AS SELECT * FROM user_info; -- 复制数据到已存在的表 INSERT INTO user_info_bak SELECT * FROM user_info;第一句会把表结构和索引都复制过去但不复制数据也不复制外键约束。第二句最省事但只复制列类型主键、自增、默认值、索引统统不复制。第三句要求目标表事先存在而且两边列结构对得上。4.2 CTAS 的三个大坑CTASCREATE TABLE AS SELECT确实省事但有三个坑必须先知道不会保留约束和默认值。复制出来的表只有列名和列类型主键没了、自增没了、NOT NULL 可能也变成可空了。所以在用它做备份表、中间表的时候如果后面要继续往里插数据最好手动补上主键和索引。不会复制索引和外键。原来的查询如果走索引跑得飞快复制出来的表没有索引同样的查询可能直接全表扫描。测试环境可能感觉不到生产环境数据一大就明显卡。如果 SELECT 里有函数或表达式列名会自动生成。比如SELECT COUNT(*) FROM orders复制出来的列名会是一个乱七八糟的名字建议写成COUNT(*) AS order_count列名清晰后续用起来不迷路。4.3 复制表的几个实用场景备份CREATE TABLE orders_bak_20240101 AS SELECT * FROM orders;要下线旧数据前先备份一份心里踏实。建空结构CREATE TABLE orders_empty AS SELECT * FROM orders WHERE 10;只想要结构、不想带数据用 WHERE 10 这种永远为假的条件。对生产表做结构模板先复制一张空表改表名改成新表再手动调整字段比从零写 CREATE TABLE 快不少。SQL Server 用户注意SQL Server 里没有 CREATE TABLE ... LIKE最接近的是 SELECT * INTO而且 SELECT * INTO 会自动建新表并复制数据。如果你只想复制结构就加 WHERE 10。顺手提一嘴SSMS 里对表右键“编写脚本为 CREATE 到”也可以生成完整建表脚本保留约束和索引适合做正式表结构复制。注意不管用哪种复制方式复制完一定要检查新表行数是否与原表一致尤其是数据量大、涉及多表 JOIN 的 SELECT。我曾经用 CTAS 复制报表源表中间 JOIN 条件写错出来的表少了 30 万行当时没核对下游报表跑了一整天才被人发现。5. 常见问题速查与排查思路这一节我把平时被问得最多的错误统一列出来留着当速查表报错的时候直接查。5.1 报错速查表报错/现象可能原因解决思路Table xxx already exists目标表已经存在DROP 掉或换表名DROP 前必须确认没在用Column xxx cannot be null必填字段没传值检查 INSERT 字段列表补上值或改默认值Duplicate entry 1 for key PRIMARY主键冲突换不冲突的 id或用 INSERT IGNORE / ON DUPLICATE KEY UPDATEData too long for column xxxvarchar 长度不够ALTER TABLE 改字段长度或先截断数据Incorrect date value: xxx日期格式不对统一用 YYYY-MM-DD 或 YYYY-MM-DD HH:MM:SS中文存入后显示问号字符集不一致统一表、连接、客户端字符集为 utf8mb4复制出来的表查询很慢索引没复制过来补建索引尤其 WHERE 和 JOIN 列插入特别慢索引/约束太多或批量过小临时去掉索引分批提交插完重建这张表不用死记报错的时候翻就行。但核心思想要记住大部分错误是表结构设计和写 SQL 的习惯问题不是数据库的锅。5.2 一个典型排查案例说个真事。有次同事跑数据同步目标表是从一张旧表复制出来的复制完直接往里插跑了一小时报了 Duplicate entry。我让他把表结构导出来看一眼结果发现新表的 id 列虽然看着是主键但并不是自增因为他用的是 CREATE TABLE AS SELECT 复制出来的自增属性压根没带过来。同步程序每次还按老逻辑不传 id数据插到内存计数附近就撞了。这种问题靠肉眼很难发现因为表名、列名都一样查 SELECT 也查不出异常。后来我们统一了流程凡是 CTAS 复制出来的表复制完第一件事就是检查约束和自增缺什么补什么。这也从侧面证明了“复制表”不是一条 SQL 就完事必要的收尾动作一个都不能少。做了这么多年 SQL 相关的工作我最大的体会是表操作才是一切的根基。查询写得再天花乱坠表定义是歪的结果就不可信表复制不明不白备份和测试环境迟早给你埋雷。最后再分享一个小习惯——正式操作前先用一条 SELECT COUNT(*) 确认一下源表行数复制或插入完成后再对一下目标表行数数据量对上了才算是真的跑完。SQL 的世界里很多麻烦都是因为少看了一眼数据量开始的。