MySQL建表与数据导入导出实战：字段类型、字符集与避坑指南

做了几年后端开发我太理解新手在 MySQL 上栽跟头的感觉了。尤其是“创建表”和“导入导出数据”这两个操作看起来很简单但真要动手做的时候光是字段类型选错、字符集没设对、导入文件路径不对这几个坑就够让人挠头半天。这篇文章就是我自己的 MySQL 学习笔记专门把创建表、导入导出数据的完整思路和实操命令整理出来内容偏实战适合刚学 MySQL 的初学者也适合平时写 SQL 经常要查资料的开发同学。我尽量把每一步背后的道理也讲清楚让大家不但会敲命令还能知道为什么要这样写。1. 建表与导入导出为什么先搞清楚这俩1.1 建表是整个数据模型的根基很多新手在刚接触 MySQL 的时候最容易犯的错误就是一上来就写INSERT觉得“能存进去、能查出来”就够了。但实际做几个项目就会明白表结构设计得好不好直接决定了后面写 SQL 是丝滑还是痛苦。比如用户表的主键用什么类型订单表的金额字段为什么不能用FLOAT状态字段到底是存数字还是存字符串这些看似不起眼的选择在数据量上来之后就是天壤之别。创建表这件事本质上是在给业务数据做“容器设计”。你设计一个字段实际上是对一类数据做约束这个字段允许什么格式最大长度是多少是否允许为空是否有默认值。约束做得越合理脏数据进来的概率就越低。比如手机号字段如果你只给它一个VARCHAR(255)那用户随手填个“abc”也能存进去但如果设置成VARCHAR(20)再加校验至少长度上能挡住一批明显不合理的输入。所以建表不是一个简单的“把字段列出来”而是一次对业务规则的前置梳理。另外建表时就要考虑好存储引擎、字符集、排序规则这些基础属性。MySQL 默认的存储引擎是 InnoDB支持事务和外键多数业务场景下都用它字符集建议用utf8mb4因为它能完整支持中文、表情符号等。很多老项目用了utf8后来发现用户昵称里带个 emoji 就存不进去只能改表字符集迁移数据这就属于建表时偷懒留下的历史债。1.2 导入导出数据的真实应用场景导入导出数据看起来只是“搬运”但它在日常开发中出现的频率非常高。最常见的场景有三类第一是数据迁移比如从测试环境同步数据到生产环境或者把旧系统的数据搬到新库第二是备份与恢复虽然生产环境通常有专业的备份系统但开发环境里用mysqldump做一次逻辑备份依然是简单有效的保底手段第三是批量数据处理比如从 Excel 或 CSV 文件里导入一批商品信息或者把线上表中的部分查询结果导出给运营同学分析。不同的场景要用的工具和命令也不一样。比如整库备份用mysqldump最方便它导出的是一堆 SQL 语句可以随时在其他环境重放但如果只是把一张表的几列数据交给别人用SELECT INTO OUTFILE导出成 CSV 更合适反过来要把一份 CSV 快读灌进表里LOAD DATA INFILE是速度最猛的方式。这些工具方法各有各的脾气用错了不一定报错但效率和效果差别很大。下面我从建表开始一条一条过。2. 创建表的完整实操与细节拆解2.1 CREATE TABLE 基础语法和步骤先看一张最简单的建表语句。假设我们要建一个“用户表”包含用户 ID、用户名、邮箱、注册时间四个字段SQL 如下CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE shop; CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID, username VARCHAR(50) NOT NULL COMMENT 用户名, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 注册时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户表;这段 SQL 看起来不长但里面的信息密度很高。先通过CREATE DATABASE建库紧接着USE切换进去这是一个防止“当前数据库不对”的好习惯。建表时每个字段我都加了COMMENT别嫌麻烦等两三个月后再回头看这张表你会感谢当时写注释的自己。主键PRIMARY KEY (id)保证每条记录能被唯一标识UNIQUE KEY uk_username (username)则保证用户名不重复。建表之后可以用SHOW CREATE TABLE user\G查看 MySQL 实际生成的表结构用DESC user;查看字段概要。这两个命令是我平时用得最多的检查手段尤其是在验证字段类型、默认值、自增属性的时候。如果发现表结构需要调整后面再通过ALTER TABLE去改比如加字段、改字段类型、加索引语法分别是ALTER TABLE user ADD COLUMN phone VARCHAR(20) DEFAULT NULL COMMENT 手机号; ALTER TABLE user MODIFY COLUMN email VARCHAR(150) NOT NULL COMMENT 邮箱; ALTER TABLE user ADD INDEX idx_email (email);但核心原则还是老话能一次性设计好就不要反复修改。频繁ALTER TABLE在大表上会锁表耗时影响线上服务。2.2 字段类型怎么选不要全用varchar我在初学 MySQL 的时候因为图省事所有字段都恨不得用VARCHAR(255)后来吃了不少亏。字段类型的选取核心原则是“用什么存什么选最合适的宽度”。下表是我整理的最常用字段类型对照按使用频率排序字段类型说明推荐场景注意事项INT/BIGINT整数类型ID、数量、年龄、状态码无符号用UNSIGNED可扩大正数范围VARCHAR(n)可变长字符串用户名、邮箱、标题n 表示字符数不是字节数按实际最大长度设置CHAR(n)定长字符串手机号、身份证号、固定编码长度固定时查询效率略高DECIMAL(p,s)精确小数金额、单价、汇率严禁用FLOAT/DOUBLE存金额会有精度丢失DATETIME/TIMESTAMP日期时间创建时间、更新时间、业务时间TIMESTAMP有 2038 年上限很多新项目更偏好DATETIMETEXT长文本文章内容、JSON 字符串不能设置默认值前缀索引也不方便JSONJSON 文档存储动态属性MySQL 5.7 支持查询可以用-语法最容易踩的坑有两个。第一个是用FLOAT存金额。二进制浮点数天然无法精确表示所有十进制小数比如0.1存进去再读出来可能变成0.100000001累计计算的时候误差会被放大。金额一律用DECIMAL(10,2)这种定点数。第二个是没有区分“字符长度”和“字节长度”。VARCHAR(255)里的 255 是字符数在utf8mb4下最多能存 255 个汉字但底层占用的字节数可能是 255×4。所以设置长度要看业务含义不要盲目给超大宽度过宽的字段会让索引变大影响查询性能。2.3 约束与索引从建表开始就要规划好约束就是数据库帮我们守门的一系列规则。常见的约束有这几种NOT NULL非空约束UNIQUE唯一约束PRIMARY KEY主键约束DEFAULT默认值约束FOREIGN KEY外键约束。还有CHECK约束MySQL 8.0.16 之前基本不生效之后版本才真正支持平时用得不算多。主键是表的灵魂。我个人的习惯是除非有极其强烈的业务语义否则一律使用自增INT或者BIGINT作为主键业务字段不做主键。为什么因为主键要稳定、唯一、短小。用手机号做主键一旦用户注销号码要换绑业务上就要改主键这种事在关系模型里非常麻烦。自增主键简单稳定聚簇索引写入又是顺序追加性能和维护性都很好。如果数据量特别大、追求分布式全局唯一 ID那可以换成雪花算法生成的BIGINT但这属于进阶话题新手阶段先把自增主键用规范即可。唯一约束通常用来保证业务唯一性比如用户名、订单号。要注意UNIQUE KEY和普通索引INDEX不是一回事前者额外带唯一性约束。建表时就要把高频查询涉及的字段规划成索引但索引不是越多越好。每张表会建立若干辅助索引会占用额外空间写入时也会增加维护成本。初期可以把唯一约束、外键关联字段、WHERE条件中非常固定的字段考虑建索引其他后补。外键在互联网业务中其实用得比较谨慎。物理外键会影响写入性能而且分库分表后基本没法用很多团队宁可只在代码层面维护关联关系也不在数据库里建FOREIGN KEY。我的建议是学习阶段要理解外键的作用但实际生产项目里可以先不建物理外键用应用层逻辑保证数据一致性等确有需要再加。2.4 字符集和存储引擎新手最容易忽略字符集这个问题往往是“平时没事一遇到中文或者 emoji 就炸”。MySQL 字符集的核心是库、表、字段三级都可以单独设置优先级是字段 表 库。如果只在库级别设置了utf8mb4但建表语句里没有指定CHARSET表会继承库的字符集通常没问题。但如果你手工执行过ALTER TABLE ... DEFAULT CHARSETutf8mb4要留意这只改表的默认值已有字段的字符集未必跟着变需要改用ALTER TABLE ... CONVERT TO CHARACTER SET。我推荐统一使用utf8mb4和utf8mb4_unicode_ci排序规则。utf8mb4是完整的 UTF-8 编码能存下四字节的 emoji 和生僻字老旧的utf8在 MySQL 里其实是非完整实现最多三字节。排序规则里的_ci表示大小写不敏感这样用户名检索时不会出现大写小写对不上。如果你对排序有特殊要求比如有些场景要区分大小写可以再单独调整字段的COLLATE。存储引擎方面当前主流就是 InnoDB。它支持事务、行级锁、崩溃恢复是 MySQL 8.0 的默认引擎也是绝大多数业务场景的正确选择。MyISAM 已经是过去式除非你维护古董库并且明确知道为什么不用事务否则不要选它。建表时用ENGINEInnoDB显式指定养成好的书写习惯也方便团队审查。3. 数据导入导出四种常用方法实操全记录3.1 mysqldump备份与迁移的首选mysqldump是 MySQL 官方提供的逻辑备份工具也是日常用得最多的导入导出方式。它有两种典型语法导出整个数据库或者只导出某张表。导出整个库把数据和建表语句都打到同一个文件里mysqldump -uroot -p --single-transaction --default-character-setutf8mb4 shop shop_backup.sql导出单张表mysqldump -uroot -p --single-transaction shop user user_backup.sql这里的参数拆开看-uroot -p是用户名和密码提示--single-transaction在 InnoDB 引擎下使用事务快照保证导出期间数据一致同时不会锁住正在写入的业务这个参数强烈建议加上--default-character-setutf8mb4保证导出的 SQL 文件里中文不乱码。导出的.sql文件其实就是一系列 SQL 语句包括建表语句和INSERT INTO。要把它导回数据库最直接的方式是在 mysql 命令行里用source命令mysql -uroot -p # 进入 mysql 后执行 mysql source /path/to/shop_backup.sql;如果目标库还不存在可能需要先在文件里或者命令行里提前CREATE DATABASE shop;因为mysqldump默认不会帮你建库除非你加了--databases参数。导出时指定--databases后备份文件里会包含CREATE DATABASE和USE语句恢复时就不用手动建库了mysqldump -uroot -p --databases shop shop_with_db.sql我用mysqldump的原则是小到中等数据量几 GB 以内的迁移、开发环境复制、单表备份它都非常合适。但数据量上了几十 GB 之后mysqldump的效率和恢复速度都会明显变差。这时候就要考虑物理备份工具或者数据导入工具了。3.2 LOAD DATA INFILE快速导入大批量文本数据如果手里有一份 CSV 或 TXT 文本文件需要快速导入 MySQL 表LOAD DATA INFILE是速度最快的方式。它比逐条执行INSERT能快出好几个数量级原理是直接把数据文件解析后成批加载减少了大量 SQL 解析和网络开销。基本语法如下LOAD DATA LOCAL INFILE /tmp/user_data.csv INTO TABLE user CHARACTER SET utf8mb4 FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (username, email, created_at);每个参数的作用都很关键LOCAL表示文件在客户端机器上不加的话表示文件在 MySQL 服务器所在机器上CHARACTER SET必须和数据文件实际编码一致否则中文会乱码FIELDS TERMINATED BY ,表示列分隔符是逗号ENCLOSED BY 表示每个字段可能用双引号包裹LINES TERMINATED BY \n表示行分隔符IGNORE 1 LINES表示跳过第一行也就是文件里的表头。这是我很推荐的一种导入方式因为 CSV 在 Excel、Python 脚本、Navicat 之间流转非常方便。但有几个前提要注意。MySQL 服务器有个系统变量secure_file_priv它限制了LOAD DATA INFILE和SELECT INTO OUTFILE只能操作指定目录下的文件。如果你的导入失败并且报错带“The MySQL server is running with the --secure-file-priv option”之类的提示说明文件不在允许目录里解决办法是查看当前配置SHOW VARIABLES LIKE secure_file_priv;如果值为/var/lib/mysql-files/就把文件放到这个目录下再执行。如果值是空字符串表示不受限制但这和 MySQL 默认安全配置不一致生产环境不建议这么改。3.3 SELECT INTO OUTFILE把查询结果导出为文件和LOAD DATA INFILE对应的导出命令是SELECT INTO OUTFILE。它可以把一张表或者任意查询结果写成文本文件最常见的用途是导出 CSV 给运营做分析。示例SELECT id, username, email, created_at INTO OUTFILE /var/lib/mysql-files/user_export.csv CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n FROM user WHERE created_at 2024-01-01;这里依然受secure_file_priv的限制导出的路径必须在该变量允许的目录内。如果不想记住服务器路径也可以用mysqldump加上--where条件导出指定数据不过格式上更适合还原而不是直接给运营看。用户体验最好的还是用 Navicat、DataGrip 这类图形工具导出 Excel但那不是命令行场景的重点后面我会提一句。SELECT INTO OUTFILE有个容易踩的坑目标文件不能已存在如果同名文件已经存在MySQL 会直接报错“File exists”。所以每次导出自定义文件时要么事先清理这个文件要么把文件名带上时间戳比如user_export_20240815.csv这样既避免冲突还能留档。3.4 mysqlimport 与图形化工具补充如果你不喜欢写一串LOAD DATA INFILE的语法MySQL 还提供了一个命令行的导入工具mysqlimport它就是LOAD DATA INFILE的封装版。用法是mysqlimport -uroot -p --local --fields-terminated-by, --fields-optionally-enclosed-by --lines-terminated-by\n shop /tmp/user_data.csv注意mysqlimport导入时默认要求文件名和表名一致。比如user_data.csv默认会导入user_data表如果要导入user表可以先把文件重命名为user.csv或者使用--ignore-lines1跳过表头。这里的参数和LOAD DATA INFILE一一对应多写几次就记住了。图形化工具也值得学会尤其是开发环境里的临时操作。Navicat 的“导入向导”支持从 Excel、CSV、JSON 等格式导入到表也可以把查询结果“导出结果”成 Excel、CSV操作门槛很低。MySQL 官方的 MySQL Workbench 在“Table Data Import Wizard”里也有类似能力。我的建议是图形工具适合小批量、交互式的数据处理适合新手观察结果脚本化的命令行方式适合定时任务、自动化部署和大量数据处理。两者都要会不要偏科。4. 踩坑实录常见问题与排查技巧4.1 服务连不上error 2002 Socket 问题刚装完 MySQL第一次敲mysql -uroot -p的时候最常见的就是下面这个报错ERROR 2002 (HY000): Cant connect to local MySQL server through socket /var/run/mysqld/mysqld.sock (2)这个报错的意思是MySQL 客户端想通过 socket 文件连接本地服务器但没找到这个文件。绝大多数原因是 MySQL 服务根本没有启动。在 Linux 上用systemctl status mysql或者service mysql status看一下服务状态如果没启动就systemctl start mysql。如果是 Docker 方式跑的 MySQL要确认容器是不是在运行docker ps docker start mysql-container-name还有一种情况是 socket 路径不一致。MySQL 默认的 socket 文件可能安装在/tmp/mysql.sock但客户端去读的是/var/run/mysqld/mysqld.sock。可以用参数指定 socket 文件连接mysql -uroot -p -S /tmp/mysql.sock不过更省心的方案是直接改用 TCP 连接到127.0.0.1mysql -uroot -p -h 127.0.0.1 -P 3306使用-h 127.0.0.1时客户端会走 TCP 而不是本地 socket能避开不少路径问题。这个技巧在排查连接类故障时很常用。4.2 secure-file-priv 限制导致导入导出失败我在测试LOAD DATA INFILE时最常遇到的报错是ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option这是 MySQL 的默认安全策略在起作用限制服务器和客户端之间对本地文件系统的读写范围。查看一下SHOW VARIABLES LIKE secure_file_priv;如果结果是某个具体目录就把要导入的文件放到那个目录如果结果是一个空值表示没有限制如果结果是NULL表示完全禁止导入导出。后面两种特别是NULL一般出现在生产服务器上是数据库管理员出于安全考虑专门设置的。在开发环境里你想用文件导入导出最合适的做法是把需要操作的文件都放到secure_file_priv指定的目录而不是去修改 MySQL 配置放开限制。修改配置文件my.cnf加入secure_file_priv 能放开限制但会让数据库面临任意文件读写的风险不建议轻易尝试。4.3 导入CSV乱码、字段对不上、语法报错CSV 导入时乱码十有八九是字符集不匹配。比如你用 Excel 另存为 CSV默认可能是 GBK 或 GB2312 编码而 MySQL 表是utf8mb4直接导入就会满屏乱码。解决方式有两个一是导入语句里指定CHARACTER SET gbk前提是文件内容确实是 GBK 编码二是先在 Notepad、VS Code 这类编辑器里把文件另存为 UTF-8 编码再按utf8mb4导入。我个人更推荐第二种因为 UTF-8 是团队协作里最通用的格式避免文件到了别人手里又乱掉。字段对不上的问题常见表现是导入成功但数据错位。比如 CSV 有 5 列但表的字段顺序是另外的而你的LOAD DATA语句里又没有列名列表MySQL 会按表结构顺序逐列匹配。所以我写LOAD DATA时永远会紧跟一个括号列名列表例如(username, email, created_at)保证文件里的列顺序和括号里的显式顺序一致而不是依赖表结构顺序。这样即便表结构后来加过字段也不会把数据插错列。还有一个容易忽略的细节是空值处理。CSV 里经常有空字段如果不处理导入后可能是空字符串而不是NULL。如果需要把空字符串转成NULL可以在LOAD DATA里用SET子句判断比如LOAD DATA LOCAL INFILE /tmp/user_data.csv INTO TABLE user CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (username, email, created_at) SET email IF(email , NULL, email);不过这里有个前提是要用用户变量占位如果记不住这些进阶语法先用 Excel 处理好空值把空单元格补成NULL或者固定占位符也能解决问题。4.4 大文件导入太慢或卡死几千行数据的导入直接INSERT可能几秒就完事了问题不大。但如果是几百万行再用一条一条的INSERT那简直是灾难。我在一次测试中导入 200 万行 CSV用逐条INSERT跑了快半个小时换成LOAD DATA LOCAL INFILE之后不到 30 秒就结束了差距非常夸张。如果LOAD DATA也慢可以从几个方向优化第一确认表上索引是否过多导入时每维护一个索引都会增加写入成本可以先把不必要的索引删掉导入完成后再重新加回来第二检查是否有触发器或额外的默认值计算逻辑如果有的话导入期间会产生额外开销第三调整 MySQL 的max_allowed_packet参数避免单个包太大被拒第四InnoDB 引擎导入时如果硬盘和内存条件允许可以适当调大innodb_buffer_pool_size。还有一个非常实用的小技巧用mysqldump导出再导入大表时可以在导入前先关闭唯一性检查和外键检查加快导入速度SET FOREIGN_KEY_CHECKS 0; SET UNIQUE_CHECKS 0; -- 执行导入 SET FOREIGN_KEY_CHECKS 1; SET UNIQUE_CHECKS 1;这个操作相当于让数据库导入时暂时不校验外键和唯一约束等导入完成后再次开启。但要注意前提是导入的数据本身是干净的否则关闭UNIQUE_CHECKS后如果混入了重复数据等下次开启唯一索引时可能会出现报错清理起来更麻烦。4.5 常见问题速查表为了以后排查方便我把自己碰到的典型问题整理成一个速查表供大家遇到时快速对照现象常见原因处理方式ERROR 2002连接失败MySQL 服务未启动启动服务用-h 127.0.0.1走 TCP 连接ERROR 1290secure-file-priv文件目录不在允许范围查看secure_file_priv把文件放到指定目录中文全部乱码字符集不匹配装载时指定CHARACTER SET或先把文件转成 UTF-8文件已存在报错SELECT INTO OUTFILE目标文件已存在清理旧文件或文件名加时间戳导入后数字变成了 0字段类型不匹配检查导入文件文本内容和表字段类型用户名重复导入报错违反唯一约束用IGNORE跳过或先清洗数据大文件导入太慢索引多、锁开销大、单条事务用LOAD DATA INFILE临时关闭外键检查或分批导入导入后自增 ID 变成大数字文件里带了 ID 列明确导入列名去掉 ID 列或重置AUTO_INCREMENT5. 我对建表和导入导出的几条个人体会5.1 建表阶段容易忽视的设计点建表这件事等到生产环境跑起来再改代价是成倍增长的。我在踩过几次坑之后总结了几个建表阶段的额外建议。第一字符串字段的默认值尽量别写成空字符串如果业务上不确定宁可允许NULL也别让空字符串混进数据里否则后面WHERE email 和WHERE email IS NULL两套逻辑会让人很痛苦。第二时间字段的默认值可以直接用DEFAULT CURRENT_TIMESTAMP更新时间字段配合ON UPDATE CURRENT_TIMESTAMP这样INSERT和UPDATE时都不用手动维护时间省事又准确。第三所有表都加上主键和合理的唯一约束不要在后续再补早期数据量小的时候无所谓等数据量大了再去清理重复数据是非常难受的。还有一点容易被忽视的是字段注释。团队协作时一个没有注释的“状态”字段过三个月没人知道1和2分别代表什么。我的习惯是不仅用COMMENT写明字段含义还会在注释里写上取值范围比如状态: 0-禁用 1-启用这样查表结构就能知道业务含义不用再翻文档找接口定义。5.2 数据导入导出中的三条黄金习惯第一任何导入操作前先备份目标表或目标库。哪怕你只是导入一份测试数据也值得先mysqldump一下原表。因为导入一旦发生错误可能不是一行数据的问题而是会牵连到关联表的数据到时候想恢复就很麻烦。第二先小批量验证再全量执行。无论是LOAD DATA INFILE还是mysqldumpsource我都习惯先导入前 100 条数据确认字段映射、编码、日期格式、空值处理都正常再放开量执行。用LIMIT导出部分数据或者手工截取 CSV 前几行成本都很低却能避免全量导入后才发现列对不上的尴尬。第三导入导出过程中的编码和路径永远用显式声明不要依赖默认值。命令里写出--default-character-setutf8mb4LOAD DATA里写出CHARACTER SETSTDOUT还是文件路径都写完整。这样虽然看起来啰嗦但一旦换到另一台服务器、另一个环境这些命令依然可以稳定复现不会因为环境差异而出现莫名其妙的乱码或路径问题。回到开头那个问题MySQL 的创建表和导入导出数据并不难难的是在动手之前把思路理清楚。表结构设计时多想一步导入导出时多做一次校验后面能省下大把排查问题的时间。我这份学习笔记也是自己反复修改、踩坑之后积累下来的希望能帮你在 MySQL 这条路上走得顺一点。