11.0 概述 386
11.1 使用LOAD DATA和mysqlimport导入数据 390

首先创建 mytbl_3 表（结构与 mytbl 相同）：sql
CREATE TABLE mytbl_3 LIKE mytbl;用文本编辑器（如 Notepad++）打开 mytbl.txt，确保格式转换成window
mysql> TRUNCATE TABLE mytbl_3;
Query OK, 0 rows affected (0.09 sec)mysql>
mysql> LOAD DATA LOCAL INFILE 'D:\\sql\\MySQL_cookbook\\mysqlcookbook-master\\recipes\\transfer\\load-examples\\mytbl.txt'INTO TABLE mytbl_3-> FIELDS TERMINATED BY ':'-> LINES TERMINATED BY '\n'-> (@col1, @col2)-> SET col1 = IF(@col1 REGEXP '^".*"$',->               TRIM(BOTH '"' FROM REPLACE(@col1, '""', '"')),->               @col1),->     col2 = IF(@col2 REGEXP '^".*"$',->               TRIM(BOTH '"' FROM REPLACE(@col2, '""', '"')),->               @col2);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0mysql> select * from mytbl_3;
+------+-------+
| col1 | col2  |
+------+-------+|bc  | def|hi  | jkl| n  | o p|m"n  | o"p"
+------+-------+
4 rows in set (0.00 sec)TRUNCATE TABLE mytbl;  -- 清空表数据
C:\Users\lenovo>mysqlimport -u cbuser -p --local --fields-terminated-by=":" --fields-optionally-enclosed-by="\"" --fields-escaped-by="\"" --lines-terminated-by="\r\n" cookbook "D:\sql\MySQL_cookbook\mysqlcookbook-master\recipes\transfer\load-examples\mytbl.txt"
Enter password: ******
cookbook.mytbl: Records: 4  Deleted: 0  Skipped: 0  Warnings: 0C:\Users\lenovo>mysql -u cbuser -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 8.0.40 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use cookbook
Database changed
mysql> select * from mytbl;
+------+------+
| col1 | col2 |
+------+------+
| abc  | def  |
| ghi  | jkl  |
| m n  | o p  |
| m"n  | o"p  |
+------+------+
4 rows in set (0.00 sec) 
成功的关键是：
正确设置了 字段分隔符（--fields-terminated-by=":"），匹配文件中的冒号分隔。
正确处理了 引号转义（--fields-optionally-enclosed-by="\"" 和 --fields-escaped-by="\""），解决了双引号的解析问题。
匹配了 Windows 系统的 行终止符（--lines-terminated-by="\r\n"），避免了空行和错位。
Windows 命令行的换行续符 ^ 需要放在每行末尾，且不能有多余的空格。mysql> describe weatherdata;
+---------+-----------------------------------------------------------+------+-----+---------+-------+
| Field   | Type                                                      | Null | Key | Default | Extra |
+---------+-----------------------------------------------------------+------+-----+---------+-------+
| station | int unsigned                                              | NO   | PRI | NULL    |       |
| type    | enum('precip','temp','cloudiness','humidity','barometer') | NO   | PRI | NULL    |       |
| value   | float                                                     | YES  |     | NULL    |       |
+---------+-----------------------------------------------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)执行命令
sql
TRUNCATE TABLE weatherdata;执行后，表中的 15 条记录会被全部删除，且无法恢复（这一点和 DELETE FROM weatherdata; 不同，DELETE 可以通过事务回滚恢复，TRUNCATE 不行）。
3. 替代方案：DELETE
如果需要更灵活的删除（比如条件删除，或保留事务回滚的可能），可以用 DELETE：sql
-- 删除所有数据（可以回滚，速度比 TRUNCATE 慢，适合小表）
DELETE FROM weatherdata;两者的主要区别：特性	TRUNCATE TABLE	DELETE FROM
速度	快（直接清空表，不记录日志）	慢（逐行删除，记录日志）
事务回滚	不可回滚（部分数据库支持，MySQL 中一般不可回滚）	可回滚（在事务中执行时）
自增计数器	重置	不重置
适用场景	彻底清空大表，无需恢复	条件删除或需要回滚的场景C:\Users\lenovo>mysql -u cbuser -p --local-infile=1
Enter password: ******Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use cookbook;
Database changed
mysql> LOAD DATA LOCAL INFILE 'D:\\sql\\MySQL_cookbook\\mysqlcookbook-master\\recipes\\transfer\\load-examples\\data.txt'-> REPLACE INTO TABLE weatherdata-> FIELDS TERMINATED BY ','  -- 假设分隔符为逗号，根据实际情况修改（如'\t'表示制表符）-> LINES TERMINATED BY '\r\n'  -- Windows 换行符-> (station, type, value);  -- 明确指定字段顺序（需与文件列顺序一致）
Query OK, 15 rows affected (0.01 sec)
Records: 15  Deleted: 0  Skipped: 0  Warnings: 0mysql> select * from weatherdata;
+---------+------------+--------+
| station | type       | value  |
+---------+------------+--------+
|       1 | precip     |   10.5 |
|       1 | temp       |   25.3 |
|       1 | cloudiness |    0.7 |
|       1 | humidity   |   65.2 |
|       1 | barometer  | 1013.2 |
|       2 | precip     |    5.2 |
|       2 | temp       |   18.7 |
|       2 | cloudiness |    0.9 |
|       2 | humidity   |   72.5 |
|       2 | barometer  | 1008.5 |
|       3 | precip     |      0 |
|       3 | temp       |   32.1 |
|       3 | cloudiness |    0.2 |
|       3 | humidity   |     35 |
|       3 | barometer  | 1020.1 |
+---------+------------+--------+
15 rows in set (0.00 sec)mysql> -- 先清空表
mysql> TRUNCATE TABLE mess_1;
Query OK, 0 rows affected (0.08 sec)mysql>
mysql> -- 执行导入，将 @state_abbrev 原始值存入 st_abbrev
mysql> LOAD DATA LOCAL INFILE 'D:\\sql\\MySQL_cookbook\\mysqlcookbook-master\\recipes\\transfer\\load-examples\\datamess.txt'-> INTO TABLE mess_1-> IGNORE 1 LINES-> (@date, @time, @name, @weight_lb, @state_abbrev)-> SET->   dt = CONCAT(@date, ' ', @time),->   first_name = SUBSTRING_INDEX(@name, ' ', 1),->   last_name = SUBSTRING_INDEX(@name, ' ', -1),->   weight_kg = @weight_lb * 0.454,->   st_abbrev = @state_abbrev;  -- 直接存入原始值，不查询 states 表
Query OK, 9 rows affected, 18 warnings (0.01 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 18mysql>
mysql> -- 查看原始州缩写值
mysql> SELECT->   CONCAT(first_name, ' ', last_name) AS full_name,->   st_abbrev AS original_state_abbrev,->   (SELECT name FROM states WHERE UPPER(TRIM(abbrev)) = UPPER(TRIM(st_abbrev))) AS matched_state_name-> FROM mess_1;
+-------------------+-----------------------+--------------------+
| full_name         | original_state_abbrev | matched_state_name |
+-------------------+-----------------------+--------------------+
| Mary Mary         | CA                    | California         |
| David David       | TX                    | Texas              |
| Emily Emily       | FL                    | Florida            |
| Michael Michael   | IL                    | Illinois           |
| Sarah Sarah       | PA                    | Pennsylvania       |
| James James       | OH                    | Ohio               |
| Lisa Lisa         | MX                    | NULL               |
| Robert Robert     | WA                    | Washington         |
| Patricia Patricia | CO                    | Colorado           |
+-------------------+-----------------------+--------------------+
9 rows in set (0.00 sec)mysql> select * from mess_1;
+---------------------+-----------+------------+-----------+-----------+
| dt                  | last_name | first_name | weight_kg | st_abbrev |
+---------------------+-----------+------------+-----------+-----------+
| 2023-02-20 14:15:00 | Mary      | Mary       |   25.5148 | CA        |
| 2023-03-05 09:45:00 | David     | David      |   31.6892 | TX        |
| 2023-04-10 16:20:00 | Emily     | Emily      |   28.2842 | FL        |
| 2023-05-18 11:00:00 | Michael   | Michael    |   37.2734 | IL        |
| 2023-06-22 13:30:00 | Sarah     | Sarah      |   27.1038 | PA        |
| 2023-07-08 07:50:00 | James     | James      |   34.2316 | OH        |
| 2023-08-15 15:10:00 | Lisa      | Lisa       |   29.4646 | MX        |
| 2023-09-30 10:25:00 | Robert    | Robert     |   36.4108 | WA        |
| 2023-10-05 17:40:00 | Patricia  | Patricia   |   26.6044 | CO        |
+---------------------+-----------+------------+-----------+-----------+
9 rows in set (0.00 sec)

11.2 导入CSV文件 401

在mysql中导入csv文件。在数据库中输入如下命令： Mysql>load data infile "文件路径\X.csv"
into table table-name 
/*如果csv文件包含中文，添加"character set gb2313"*/
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'
注:出现过所有数据进入同一列的情况，上述代码去掉最后一行，导入正常了。

11.3 导出MYSQL的查询结果

mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| secure_file_priv | D:\software\MySql\Data\Uploads\ |
+------------------+---------------------------------+
1 row in set, 1 warning (0.02 sec)mysql> SELECT * FROM passwd-> INTO OUTFILE 'D:/software/MySql/Data/Uploads/passwd.txt'  -- 使用 MySQL 允许的路径-> FIELDS TERMINATED BY '\t'  -- 可选：指定字段分隔符（如制表符）-> LINES TERMINATED BY '\r\n';  -- Windows 换行符
Query OK, 58 rows affected (0.01 sec)当你以 root 身份执行这个命令时：bash
mysql -u root -p -e "select account, shell from passwd" --skip-column-names cookbook > shells.txt
生成的 shells.txt 文件会保存在 当前命令行的工作目录 下。 C:\Users\lenovo> 如果需要指定保存到其他目录（如桌面），可以在文件名前加上完整路径，例如：
bash
mysql -u root -p -e "select account, shell from passwd" --skip-column-names cookbook > "C:\Users\lenovo\Desktop\shells.txtC:\Users\lenovo>mysql --skip-column-names -e "SELECT * FROM city" -u cbuser -p cookbook
Enter password: ******
+----------------+----------------+----------------+
|        Alabama | Montgomery     | Birmingham     |
|         Alaska | Juneau         | Anchorage      |
|        Arizona | Phoenix        | Phoenix        |
|       Arkansas | Little Rock    | Little Rock    |
|     California | Sacramento     | Los Angeles    |
|       Colorado | Denver         | Denver         |
|    Connecticut | Hartford       | Bridgeport     |
|       Delaware | Dover          | Wilmington     |
|        Florida | Tallahassee    | Jacksonville   |
|        Georgia | Atlanta        | Atlanta        |
|         Hawaii | Honolulu       | Honolulu       |
|          Idaho | Boise          | Boise          |
|       Illinois | Springfield    | Chicago        |
|        Indiana | Indianapolis   | Indianapolis   |
|           Iowa | Des Moines     | Des Moines     |
|         Kansas | Topeka         | Wichita        |
|       Kentucky | Frankfort      | Louisville     |
|      Louisiana | Baton Rouge    | New Orleans    |
|          Maine | Augusta        | Portland       |
|       Maryland | Annapolis      | Baltimore      |
|  Massachusetts | Boston         | Boston         |
|       Michigan | Lansing        | Detroit        |
|      Minnesota | St. Paul       | Minneapolis    |
|    Mississippi | Jackson        | Jackson        |
|       Missouri | Jefferson City | Kansas City    |
|        Montana | Helena         | Billings       |
|       Nebraska | Lincoln        | Omaha          |
|         Nevada | Carson City    | Las Vegas      |
|  New Hampshire | Concord        | Manchester     |
|     New Jersey | Trenton        | Newark         |
|     New Mexico | Santa Fe       | Albuquerque    |
|       New York | Albany         | New York City  |
| North Carolina | Raleigh        | Charlotte      |
|   North Dakota | Bismarck       | Fargo          |
|           Ohio | Columbus       | Columbus       |
|       Oklahoma | Oklahoma City  | Oklahoma City  |
|         Oregon | Salem          | Portland       |
|   Pennsylvania | Harrisburg     | Philadelphia   |
|   Rhode Island | Providence     | Providence     |
| South Carolina | Columbia       | Columbia       |
|   South Dakota | Pierre         | Sioux Falls    |
|      Tennessee | Nashville      | Memphis        |
|          Texas | Austin         | Houston        |
|           Utah | Salt Lake City | Salt Lake City |
|        Vermont | Montpelier     | Burlington     |
|       Virginia | Richmond       | Virginia Beach |
|     Washington | Olympia        | Seattle        |
|  West Virginia | Charleston     | Charleston     |
|      Wisconsin | Madison        | Milwaukee      |
|        Wyoming | Cheyenne       | Cheyenne       |
+----------------+----------------+----------------+打开 PowerShell（按下 Win + X 选择 “Windows PowerShell”），执行以下命令：
PS C:\Users\lenovo> mysql -u cbuser -p -N -e "SELECT * FROM city" cookbook | ForEach-Object { $_ -replace "`t", '#' } > "C:\Users\lenovo\city.txt"
Enter password: ******
PS C:\Users\lenovo>
命令解释
mysql -u cbuser -p -N -e "SELECT * FROM city" cookbook：
-N 等价于 --skip-column-names，不显示列名。
执行查询并输出结果（默认用制表符 Tab 分隔字段）。
ForEach-Object { $_ -replace "t", '#' }`：
PowerShell 的文本替换功能，将制表符 Tab（\t 在 PowerShell 中用 `t 表示）替换为 #。
> "C:\Users\lenovo\city.txt"：
将处理后的结果保存到 city.txt，文件位于 C:\Users\lenovo\ 目录（即你的用户主目录）。