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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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)，后来吃了不少亏。字段类型的选取，核心原则是“用什么存什么，选最合适的宽度”。下表是我整理的最常用字段类型对照，按使用频率排序：

最容易踩的坑有两个。第一个是用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 CHARSET=utf8mb4，要留意这只改表的默认值，已有字段的字符集未必跟着变，需要改用ALTER TABLE ... CONVERT TO CHARACTER SET。

我推荐统一使用utf8mb4和utf8mb4_unicode_ci排序规则。utf8mb4是完整的 UTF-8 编码，能存下四字节的 emoji 和生僻字；老旧的utf8在 MySQL 里其实是非完整实现，最多三字节。排序规则里的_ci表示大小写不敏感，这样用户名检索时不会出现大写小写对不上。如果你对排序有特殊要求，比如有些场景要区分大小写，可以再单独调整字段的COLLATE。

存储引擎方面，当前主流就是 InnoDB。它支持事务、行级锁、崩溃恢复，是 MySQL 8.0 的默认引擎，也是绝大多数业务场景的正确选择。MyISAM 已经是过去式，除非你维护古董库并且明确知道为什么不用事务，否则不要选它。建表时用ENGINE=InnoDB显式指定，养成好的书写习惯，也方便团队审查。

3. 数据导入导出：四种常用方法实操全记录

3.1 mysqldump：备份与迁移的首选

mysqldump是 MySQL 官方提供的逻辑备份工具，也是日常用得最多的导入导出方式。它有两种典型语法：导出整个数据库，或者只导出某张表。

导出整个库，把数据和建表语句都打到同一个文件里：

mysqldump -uroot -p --single-transaction --default-character-set=utf8mb4 shop > shop_backup.sql

导出单张表：

mysqldump -uroot -p --single-transaction shop user > user_backup.sql

这里的参数拆开看：-uroot -p是用户名和密码提示；--single-transaction在 InnoDB 引擎下使用事务快照，保证导出期间数据一致，同时不会锁住正在写入的业务，这个参数强烈建议加上；--default-character-set=utf8mb4保证导出的 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-lines=1跳过表头。这里的参数和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): Can't 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.1：

mysql -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 常见问题速查表

为了以后排查方便，我把自己碰到的典型问题整理成一个速查表，供大家遇到时快速对照：

5. 我对建表和导入导出的几条个人体会

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还是mysqldump+source，我都习惯先导入前 100 条数据，确认字段映射、编码、日期格式、空值处理都正常，再放开量执行。用LIMIT导出部分数据，或者手工截取 CSV 前几行，成本都很低，却能避免全量导入后才发现列对不上的尴尬。

第三，导入导出过程中的编码和路径，永远用显式声明，不要依赖默认值。命令里写出--default-character-set=utf8mb4，LOAD DATA里写出CHARACTER SET，STDOUT还是文件路径都写完整。这样虽然看起来啰嗦，但一旦换到另一台服务器、另一个环境，这些命令依然可以稳定复现，不会因为环境差异而出现莫名其妙的乱码或路径问题。

回到开头那个问题，MySQL 的创建表和导入导出数据并不难，难的是在动手之前把思路理清楚。表结构设计时多想一步，导入导出时多做一次校验，后面能省下大把排查问题的时间。我这份学习笔记也是自己反复修改、踩坑之后积累下来的，希望能帮你在 MySQL 这条路上走得顺一点。