MySQL导出表到文本文件字段名称

Question

假设我在 MySQL 中有下表

create table test_tbl
(
  col1 varchar(100),
  col2 varchar(100),
  amount int,
  created datetime
)

数据插入

Insert into test_tbl values('unu', 'doi', 10, '05/01/2015');
Insert into test_tbl values('patru', trei', 400, '04/01/2015');

我需要按以下格式导出该表中的所有数据。该文件应该是txt文件。

"col1"="unu","col2"="doi","amount"="10","created"="05/01/2015"
"col1"="patru","col2"="trei","amount"="400","created"="04/01/2015"

所以逻辑是:每个列名称及其值以逗号分隔。

在MySQL中可能得到这样的结果吗？

Answer 1

用于导出表的数据-

SELECT  CONCAT('"col1"="',col1,'","col2"="',col2,'","amount"="',amount,'","created"="',DATE_FORMAT(created,'%d/%m/%Y'),'"') t  FROM test_tbl INTO OUTFILE '/tmp/test.txt' CHARACTER SET latin1 FIELDS ENCLOSED BY '' LINES TERMINATED BY '\r\n';

用于从 csv 导入表格

    mysql> CREATE TABLE `test_tbl` (
    ->   `col1` varchar(100) DEFAULT NULL,
    ->   `col2` varchar(100) DEFAULT NULL,
    ->   `amount` int DEFAULT NULL,
    ->   `created` datetime DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    -> ;
Query OK, 0 rows affected (0.44 sec)

mysql> load data local infile 'test.txt' into table test_tbl fields terminated by ',' ENCLOSED BY '"' lines terminated by '\r\n' (@col1, @col2,@col3,@col4)
    -> set col1 = substr(@col1,8), col2 = substr(@col2,8), amount = substr(@col3,10), created = str_to_date(substr(@col4,11), '%d/%m/%Y');
Query OK, 2 rows affected (0.09 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from test_tbl;
+-------+------+--------+---------------------+
| col1  | col2 | amount | created             |
+-------+------+--------+---------------------+
| unu   | doi  |     10 | 2015-01-05 00:00:00 |
| patru | trei |    400 | 2015-01-04 00:00:00 |
+-------+------+--------+---------------------+
2 rows in set (0.00 sec)