对于未来的读者,如果他们希望使用bash批量导出,可以采用以下简单方法,该方法在mariadb上进行过测试,同样适用于mysql。
akshay@ideapad:/tmp$ mysql -u someuser -p test -e "select * from offices"
Enter password:
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| officeCode | city | phone | addressLine1 | addressLine2 | state | country | postalCode | territory |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| 1 | San Francisco | +1 650 219 4782 | 100 Market Street | Suite 300 | CA | USA | 94080 | NA |
| 2 | Boston | +1 215 837 0825 | 1550 Court Place | Suite 102 | MA | USA | 02107 | NA |
| 3 | NYC | +1 212 555 3000 | 523 East 53rd Street | apt. 5A | NY | USA | 10022 | NA |
| 4 | Paris | +33 14 723 4404 | 43 Rue Jouffroy D'abbans | NULL | NULL | France | 75017 | EMEA |
| 5 | Tokyo | +81 33 224 5000 | 4-1 Kioicho | NULL | Chiyoda-Ku | Japan | 102-8578 | Japan |
| 6 | Sydney | +61 2 9264 2451 | 5-11 Wentworth Avenue | Floor #2 | NULL | Australia | NSW 2010 | APAC |
| 7 | London | +44 20 7877 2041 | 25 Old Broad Street | Level 7 | NULL | UK | EC2N 1HN | EMEA |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
如果您是以非root用户身份进行导出,则请按以下方式设置权限
root@ideapad:/tmp# mysql -u root -p
MariaDB[(none)]> UPDATE mysql.user SET File_priv = 'Y' WHERE user='someuser' AND host='localhost';
重新启动或重新加载mysqld
akshay@ideapad:/tmp$ sudo su
root@ideapad:/tmp
示例代码片段
akshay@ideapad:/tmp$ cat test.sh
user="someuser"
password="password"
database="test"
mysql -u"$user" -p"$password" "$database" <<EOF
SELECT *
INTO OUTFILE '/tmp/csvs/offices.csv'
FIELDS TERMINATED BY '|'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM offices;
EOF
执行
akshay@ideapad:/tmp$ mkdir -p /tmp/csvs
akshay@ideapad:/tmp$ chmod +x test.sh
akshay@ideapad:/tmp$ ./test.sh
akshay@ideapad:/tmp$ cat /tmp/csvs/offices.csv
"1"|"San Francisco"|"+1 650 219 4782"|"100 Market Street"|"Suite 300"|"CA"|"USA"|"94080"|"NA"
"2"|"Boston"|"+1 215 837 0825"|"1550 Court Place"|"Suite 102"|"MA"|"USA"|"02107"|"NA"
"3"|"NYC"|"+1 212 555 3000"|"523 East 53rd Street"|"apt. 5A"|"NY"|"USA"|"10022"|"NA"
"4"|"Paris"|"+33 14 723 4404"|"43 Rue Jouffroy D'abbans"|\N|\N|"France"|"75017"|"EMEA"
"5"|"Tokyo"|"+81 33 224 5000"|"4-1 Kioicho"|\N|"Chiyoda-Ku"|"Japan"|"102-8578"|"Japan"
"6"|"Sydney"|"+61 2 9264 2451"|"5-11 Wentworth Avenue"|"Floor #2"|\N|"Australia"|"NSW 2010"|"APAC"
"7"|"London"|"+44 20 7877 2041"|"25 Old Broad Street"|"Level 7"|\N|"UK"|"EC2N 1HN"|"EMEA"