有次帮同事排查数据迁移问题他按照网上教程导出一份订单表再导入测试库结果发现数据量对不上中文全部变成问号主键ID也乱了。排查到最后问题出在建表时就埋下的隐患——字段字符集不统一加上导出时用了傻瓜式工具没注意选项。那次之后我就觉得MySQL的建表和导入导出看着基础但细节非常多值得踏踏实实整理一份笔记。这篇笔记围绕MySQL使用频率最高的三件事展开创建表、导出数据、导入数据。内容覆盖常用SQL语法、命令行的完整用法、图形化工具的注意事项还穿插了生产环境里常见的坑和排查思路。适合刚接触MySQL的初学者也适合写过一段时间SQL但没系统整理过这部分的开发、运维和数据同学。1. 建表前先拍板三件事存储引擎、字符集和字段类型建表不是写一句CREATE TABLE就完事。表结构设计阶段做的几个决定直接影响后续导入导出是否顺滑、查询性能是否达标、数据是否会出现乱码。这三个决定分别是存储引擎、字符集和字段类型。1.1 存储引擎选InnoDB还是MyISAM为什么默认就是InnoDBMySQL支持多种存储引擎最常见的是InnoDB和MyISAM。MySQL 5.5之后默认引擎就是InnoDB8.0更是彻底把MyISAM边缘化了。如果拿不准无脑选InnoDB基本不会错但还是要说清楚它强在哪里。InnoDB支持事务也就是ACID特性。这意味着你在导入大量数据时可以用事务包裹失败可以整体回滚不会出现导到一半数据残缺的情况。InnoDB还支持行级锁高并发写入时不会整表锁死这对线上业务至关重要。外键约束也是InnoDB独有的如果表与表之间有强关联MyISAM直接做不了。MyISAM虽然查得快、占用空间小但不支持事务崩溃恢复能力差写操作会锁表。实测下来大批量导入数据时MyISAM表面上看速度快但一旦中断表损坏的概率比InnoDB高很多。团队做数据迁移时最怕的就是导到一半机器重启表直接标记为crashed。所以建表语句里如果没有特殊需求显式加上ENGINEInnoDB是个好习惯虽然不写也是默认值但写出来能让看表结构的人一眼明白设计意图。1.2 字符集统一用utf8mb4别再用utf8了字符集这个坑我见得太多了。很多初学者建表时拷贝网上的老教程写的还是DEFAULT CHARSETutf8结果遇到表情符号或者生僻字就报错或者导入数据后变成一串???。MySQL里的utf8其实是utf8mb3最多只能存3字节的字符而emoji这些4字节字符根本存不进去。utf8mb4才是真正意义上完整的UTF-8编码向下兼容utf8mb3。MySQL 8.0已经默认把字符集改成utf8mb4但如果你在旧版本上建表或者从老库导出一定要手动确认。建表时建议同时指定字符集和排序规则CREATE TABLE user ( id int NOT NULL AUTO_INCREMENT, nickname varchar(50) NOT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT用户基础表;排序规则里utf8mb4_0900_ai_ci是8.0默认的不区分大小写、不区分重音日常业务够用。如果排序和索引有特殊要求再去研究bin、unicode_ci这些变体。1.3 字段类型选不对导入导出都会变麻烦字段类型这里只说三个最容易出问题的选择。第一个是整数类型。业务主键用BIGINT还是INT单表数据量预期超过20亿就必须用BIGINT这个没有商量的余地。INT最大就到21亿多一旦满了插入直接报错。数据量不大的小项目用INT就够了BIGINT占8字节索引也更大没必要浪费。第二个是小数。金额这类精确计算的字段一定要用DECIMAL比如DECIMAL(10,2)表示总位数10位、小数2位。千万别用FLOAT或DOUBLE浮点数在计算机里是近似值累加事务金额时会对不上账导出去再导回来精度就变了。第三个是字符串。短文本用VARCHAR长文本用TEXT。VARCHAR(255)以内的字段可以建索引TEXT类型必须指定前缀长度才能建索引。而且TEXT类型在旧版本MySQL里不能有默认值8.0.13之后才放开老项目里容易踩这个坑。就我个人的使用习惯长度能控制在255以内就尽量用VARCHAR。2. CREATE TABLE 的完整细节从基础建表到复制表结构的几种姿势存储引擎、字符集、字段类型这三大件敲定后写建表语句就很顺了。这一章把CREATE TABLE的常用写法和后续改动表结构的操作捋一遍。2.1 标准建表语句的结构拆解一条标准的建表语句由表名、字段定义、表级约束、表选项四部分组成。看一个完整例子CREATE TABLE IF NOT EXISTS orders ( id bigint NOT NULL AUTO_INCREMENT COMMENT 订单ID, order_no varchar(32) NOT NULL COMMENT 订单编号, user_id bigint NOT NULL COMMENT 用户ID, amount decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, status tinyint NOT NULL DEFAULT 0 COMMENT 状态0待支付 1已支付 2已取消, remark varchar(500) DEFAULT NULL COMMENT 备注, 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_order_no (order_no), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;拆开说几个容易忽略的地方。IF NOT EXISTS是兜底的好习惯重复执行脚本不会报错。AUTO_INCREMENT字段必须是索引通常是主键。DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP是MySQL的自动时间管理插入时自动写当前时间更新时自动刷新少写不少代码。注释COMMENT一定要写。表注释、字段注释都写上三个月后回头看表结构你会感谢自己当初多打的这几行字。很多团队用工具生成接口文档注释就是文档的数据源。2.2 主键、唯一键和普通索引怎么选主键最简单一张表一个一般就是自增ID。唯一键用于业务上不允许重复的字段比如order_no订单编号。普通索引用于加速查询的字段比如user_id。这里有个实战建议唯一键和普通索引的命名规范尽量统一用uk_和idx_前缀后面跟字段名。不用规范的后果就是表多了之后分不清哪个索引是干嘛的删索引都提心吊胆。组合索引要记住最左前缀原则。比如建了KEY idx_user_status(user_id, status)那么WHERE user_id?能走索引WHERE user_id? AND status?也能走索引但单独WHERE status?走不了这个索引。所以组合索引里字段顺序要按查询频率和区分度来排区分度高的放前面。2.3 复制表结构的两种方式应用场景完全不同日常工作中经常需要复制表结构比如给大表做归档、临时表测试、搭建分表结构。两种常用方式各有适用场景。第一种只复制结构不复制数据CREATE TABLE orders_archive LIKE orders;LIKE方式会完整保留原表的字段、索引、约束、自增属性包括AUTO_INCREMENT的当前值。这是最推荐的方式结构一模一样不会漏掉索引。第二种复制结构和数据CREATE TABLE orders_bak AS SELECT * FROM orders;但AS SELECT方式有几个坑不会复制索引、不会复制主键、默认值也会丢失只是单纯把列定义和数据带过来。适合创建临时分析表不适合做正式的分表或备份表。想要复制结构和数据又保留索引正确做法是先LIKE建表再INSERT INTO SELECT。2.4 ALTER TABLE修改结构的常用语句表建好后不可能一成不变业务迭代必然要加字段、改字段。高频操作汇总如下-- 添加字段 ALTER TABLE orders ADD COLUMN pay_time datetime DEFAULT NULL COMMENT 支付时间; -- 修改字段类型和默认值 ALTER TABLE orders MODIFY COLUMN status tinyint NOT NULL DEFAULT 1 COMMENT 状态1待支付 2已支付; -- 重命名字段 ALTER TABLE orders CHANGE COLUMN remark note varchar(500) DEFAULT NULL COMMENT 备注; -- 删除字段 ALTER TABLE orders DROP COLUMN note; -- 添加索引 ALTER TABLE orders ADD INDEX idx_created_at (created_at); -- 删除索引 ALTER TABLE orders DROP INDEX idx_created_at;注意CHANGE和MODIFY的区别。CHANGE后面要写两次列名一次旧列名一次新列名适合重命名的场景。MODIFY只需要写列名和新的定义适合改类型、改默认值。两者都能改字段属性只是CHANGE多一个改名能力。大表上执行ALTER TABLE要格外小心。MySQL 8.0支持了INSTANT算法有些加字段操作秒回但修改字段类型、缩字段长度这些操作会锁表重建线上大表请谨慎执行建议在业务低峰期做。3. 导数据的三条路线mysqldump、SELECT INTO OUTFILE 与图形化工具导入导出数据的方案选择取决于数据量大小、源库目标库环境、以及你手头有什么工具。我把常用的三条路线放到一起对比各自优缺点和适用场景说清楚。导出方式适用规模输出格式速度依赖条件mysqldump 命令行中大型SQL脚本中等服务器本机或远程可连SELECT INTO OUTFILE大型文本文件(CSV等)快需要secure_file_priv放行Navicat等图形工具小型SQL或Excel慢本机安装客户端3.1 mysqldump最正统的导出方式完整保留表结构mysqldump是MySQL自带的命令行工具最大的优点是导出的SQL文件同时包含建表语句和INSERT数据拿到另一个环境直接执行就能完整还原索引、约束、自增属性全都在。最基本的用法# 导出整个库 mysqldump -u root -p database_name /data/backup/database_name.sql # 导出指定表 mysqldump -u root -p database_name table_name /data/backup/table_name.sql # 导出多个表 mysqldump -u root -p database_name table1 table2 /data/backup/tables.sql生产环境导出一定要加几个参数才安全mysqldump -u root -p --single-transaction --set-gtid-purgedOFF --default-character-setutf8mb4 database_name table_name /data/backup/table_name.sql--single-transaction的意思是导出期间使用InnoDB的一致性快照不锁表线上业务可以正常读写。这个参数只对InnoDB表有效如果表是MyISAM还是会锁表。--set-gtid-purgedOFF是为了兼容那些不需要GTID的目标库特别是在有主从复制的环境里不带这个参数导出再导入会报错。--default-character-setutf8mb4这个太重要了不指定的话导出的文件可能用系统的默认字符集中文导入后就乱码。只导出表结构不带数据用--no-datamysqldump -u root -p --no-data database_name table_name /data/backup/table_structure.sql3.2 SELECT INTO OUTFILE适合大数据量的文本导出mysqldump在大数据量下导出速度不够理想自己拼SQL把数据输出为文本文件往往更快。SELECT INTO OUTFILE是MySQL直接把查询结果写进服务器上的文件。SELECT * FROM orders INTO OUTFILE /var/lib/mysql-files/orders.csv FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n;这里有个硬性的安全限制就是secure_file_priv参数。这个参数如果设置为NULLSELECT INTO OUTFILE直接不能用设置为空字符串表示不限制目录设置为具体路径比如/var/lib/mysql-files/导出文件只能放在这个目录里。查看当前值SHOW VARIABLES LIKE secure_file_priv;Windows系统下路径要用正斜杠比如C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/orders.csv。导出后文件是CSV格式用Excel可以直接打开也可以给其他系统做数据交换。实测数据量在百万级时SELECT INTO OUTFILE比mysqldump快不少因为减少了SQL语句解析和网络传输的开销。但要注意这个方式只能导出数据结构需要另外用SHOW CREATE TABLE或者mysqldump --no-data来拿。3.3 图形化工具导出简单但别忽视选项Navicat和MySQL Workbench是我见过用得最多的图形化工具适合数据量小、不想敲命令的场景。Navicat里导出表的常规路径右键点击表名 - 导出向导 - 选择格式(SQL、CSV、Excel) - 选字段 - 设置选项 - 开始。Workbench类似在表的右键菜单里有Table Data Export Wizard。用图形工具导出小数据量的表非常方便但有两个问题要注意。第一用SQL格式导出时默认可能会带DROP TABLE语句导入到目标库时直接把你现有的表干掉非常危险。导出选项里有个包含DROP TABLE的勾选框默认是勾上的建议去掉。第二大批量数据用图形工具导出时内存占用高、速度慢而且中途断掉没有断点续导还是命令行方案更稳。对比下来我的建议很简单数据量小、随手倒腾用图形工具正经做数据迁移、备份恢复用mysqldump数据量上百万、目标系统要文本文件选SELECT INTO OUTFILE。4. 导数据回去的三条对应路径和最容易出错的操作习惯导出只是上半场导入才是真正考验耐心的地方。数据导回去的方式和导出是配套的mysqldump导出的SQL文件用mysql命令还原OUTFILE导出的CSV用LOAD DATA INFILE载入图形工具导出的文件用对应的导入向导。4.1 mysql命令行导入SQL文件mysqldump导出的文件是文本格式的SQL脚本还原方式非常简单mysql -u root -p database_name /data/backup/table_name.sql如果SQL文件里包含建库语句也可以直接导入到MySQL但不指定数据库mysql -u root -p /data/backup/database_name.sql在mysql客户端里用source命令同样可以执行SQL脚本source /data/backup/table_name.sqlsource命令的好处是能实时看到执行进度和报错信息适合排查问题。mysql命令重定向的优点是适合脚本自动化输出简洁。执行导入时建议加--default-character-setutf8mb4参数mysql -u root -p --default-character-setutf8mb4 database_name /data/backup/table_name.sql不加这个参数如果系统默认字符集不是utf8mb4中文数据可能乱码。这个坑在Linux服务器上很常见因为很多发行版默认locale是POSIX或C字符集是ASCII导出的SQL文件里的中文字符会被错误解析。4.2 LOAD DATA INFILE批量导入的最优解对应OUTFILE导出的CSV文件导入用LOAD DATA INFILE。这张表是核心用法LOAD DATA INFILE /var/lib/mysql-files/orders.csv INTO TABLE orders FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 ROWS;IGNORE 1 ROWS是跳过CSV文件的第一行通常表头行不需要导入。如果你的CSV没有表头就不要写。字段顺序如果和表字段顺序不一致可以指定列LOAD DATA INFILE /var/lib/mysql-files/orders.csv INTO TABLE orders (id, order_no, user_id, amount, status) FIELDS TERMINATED BY ,;大数据量导入时LOAD DATA INFILE是速度最快的方案比一条条INSERT快一个数量级。原理上它是把数据文件直接按格式解析后写入存储引擎少了SQL层的语法解析和网络往返。4.3 导入时最容易忽略的性能开关和约束开关导入大批量数据时有几个开关建议在导入前临时关掉跑完再恢复。SET FOREIGN_KEY_CHECKS0; SET UNIQUE_CHECKS0; SET SQL_LOG_BIN0;FOREIGN_KEY_CHECKS0用于关闭外键检查。如果表之间有外键关联导入子表时父表数据还没到位就会报外键约束失败。关闭后导入顺序就不重要了导完再重新开启。UNIQUE_CHECKS0用于关闭唯一键检查减少索引维护开销导入速度能提升不少。但对于有唯一约束的业务表导入后一定要做一次重复数据检查防止脏数据混进去。SQL_LOG_BIN0是关闭二进制日志记录。如果这台MySQL有主从复制导入的时候不想让从库重复执行可以关掉binlog。但这个方法要谨慎使用属于非常规操作重要数据我还是建议正常记录binlog方便回溯。4.4 图形化工具导入的小心机Navicat导入数据和导出是对应的右键表名 - 导入向导 - 选择和导出匹配的格式 - 映射字段 - 开始。用图形工具导入时最容易犯的错是字段映射对不上。CSV文件里的列顺序和表结构不一致或者列名不同导入向导会默认按位置映射结果把手机号导进了备注字段。手动检查一遍映射关系花不了两分钟但能省很多事后修正的力气。另一个高频问题是Excel里的日期格式。Excel显示的是2024-01-01但底层存的可能是类似45000这样的序列号。转成CSV再导入时日期列很容易变成乱码或者格式错误。我的经验是用Excel处理过的数据导入前先筛一遍日期列看看格式是不是正常。5. 导入导出过程中的高频报错和我的排查顺序这一章把实操中遇到的典型报错和排查思路整理出来。这些坑如果你都提前知道能省下好几个小时的排查时间。5.1 中文乱码的连锁排查乱码是导入导出最高频的问题排查其实有固定顺序。第一步确认源库的表和数据本身的字符集第二步确认导出时的客户端连接字符集第三步确认文件的实际编码第四步确认导入时的连接字符集和目标表字符集。常用检查命令-- 查看表字符集 SHOW TABLE STATUS LIKE orders; -- 查看字段字符集 SHOW FULL COLUMNS FROM orders; -- 查看当前连接的字符集 SHOW VARIABLES LIKE character_set_connection;大多数乱码的本质是环节不统一。mysqldump导出的文件是UTF-8编码但你在Windows上用记事本打开可能显示正常用Navicat导入时如果连接字符集设置成GBK中文字符就全乱了。解决思路就是全链路统一建表用utf8mb4导出指定utf8mb4导入也指定utf8mb4。5.2 secure_file_priv导致的文件读写失败SELECT INTO OUTFILE或LOAD DATA INFILE报ERROR 1290就是secure_file_priv限制导致的。ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement解决办法是修改my.cnf把secure_file_priv指向一个允许的目录或者设为空字符串表示不限制[mysqld] secure_file_priv改完重启MySQL生效。生产环境为了安全不太建议完全放开指定一个专门目录比如/var/lib/mysql-files/然后把导出文件都在这个目录下操作既够用又不会把服务器文件系统暴露给SQL注入。5.3 导入时主键冲突和自增列错乱往已有数据的表导入数据时经常遇到主键冲突。报错类似Duplicate entry 1001 for key PRIMARY。原因通常是源表和目标表的主键范围有重叠或者导入文件里包含已经存在的主键。解决思路分两种。第一种如果目标是空表或者已经有了相同ID的数据不想保留目标表的数据可以先TRUNCATE或DELETE清空再导入。第二种如果数据要保留只是需要跳过冲突可以加IGNORE选项INSERT IGNORE INTO target_table SELECT * FROM source_table;但IGNORE会忽略所有错误不只是主键冲突慎用。自增列错乱是另一个隐藏问题。导入数据后表的AUTO_INCREMENT值可能没有正确更新导致后续插入数据时主键重复。执行一下ALTER TABLE orders AUTO_INCREMENT 1;不需要手动设置具体值MySQL会自动取当前最大ID加1。8.0版本的InnoDB自增值是持久化的导入后一般不会出问题但5.7及更早版本确实存在自增值回退的情况导入后检查一下更稳妥。5.4 导入大量数据时的中断恢复策略导入几百万行数据跑到一半断掉是最崩溃的事情。如果SQL文件是用mysqldump导出的里面通常没有包裹事务每行INSERT都是单独提交中断后已导入的数据会保留重新执行又会导致主键冲突。我的做法是导入前先记录当前表的最大ID导入后如果中断清理掉新增部分再重试-- 记录导入前最大ID SELECT MAX(id) FROM orders; -- 导入失败后清理导入的数据 DELETE FROM orders WHERE id 之前记录的最大ID; -- 重新导入 mysql -u root -p database_name /data/backup/orders.sql更稳妥的方案是导入前给目标表做个备份快照无论是用mysqldump导出一次还是直接复制表文件适合归档场景至少能让自己有心安理得的回滚路径。5.5 大表导入时间过长的优化思路几十万行数据导入需要几分钟几百万行可能就要半小时以上。如果你遇到导入慢按这个优先级检查。第一优先看是否关了外键检查和唯一键检查这个前面说过影响最大。第二看是否每次INSERT都自动提交如果SQL文件里没有显式开启事务可以包一层START TRANSACTION; ...INSERT语句... COMMIT;减少提交次数能从本质上降低磁盘刷盘次数。第三看是否用了批量INSERTmysqldump导出时默认会用扩展INSERT一条语句插入多行数据效率很高。如果你手写导入脚本也尽量用INSERT INTO ... VALUES (),(),()这种多值形式比单条INSERT快很多。第四看索引导入前把非必要索引先删掉导入完再重建对超大表有明显提速效果。我在实际工作中做百万级数据导入通常组合拳是关闭外键检查、关闭唯一键检查、包一个大事务、导入完再重建索引。整个流程下来比默认设置快了三倍以上。最后分享一个执行导入导出前的小习惯无论是用mysqldump还是LOAD DATA我在执行前都会先跑一条SELECT COUNT(*)确认源数据行数导完再数一遍目标表行数。两边对上了才敢说迁移完成。这个方法不高级但在多次数据迁移中帮我及时发现漏导、重复导的问题。另一个小技巧是导出文件命名时带上日期和来源环境比如orders_20241205_prod.sql归档的时候不会被搞混。数据文件这东西时间一长就分不清哪个是最新的、哪个是从哪个环境来的命名规范能省掉很多麻烦。MySQL的建表和导入导出本质上就是围绕结构和数据两个维度的搬运工作。把基础细节搞扎实知道每条命令背后为什么这么设计遇到问题就能快速定位而不是靠试错碰运气。