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\ 目錄（即你的用戶主目錄）。
