• 利用mysqldump命令导出为csv格式文件


    解决方法

    先导出为txt文件,其内容是以逗号“,”分隔的,得到txt文件后,再自行处理为.csv或者.xls文件。

    参数说明:

    -t, --no-create-info   Don't write table creation info.

    -T, --tab=name Create tab-separated textfile for each table to given path. (Create .sql and .txt files.)  NOTE: This only works if mysqldump is run on the same machine as the mysqld server.

    --fields-terminated-by=name  Fields in the output file are terminated by the given string.

    --fields-enclosed-by=name   Fields in the output file are enclosed by the given character.

    1. 先查看可导出位置(不然会涉及到权限问题)

    mysql> show variables like 'secure_file_priv';
    +------------------+-----------------------+
    | Variable_name    | Value                 |
    +------------------+-----------------------+
    | secure_file_priv | /var/lib/mysql-files/ |   //如果出现权限问题,尝试把该目录所有者改成mysql
    +------------------+-----------------------+
    1 row in set (0.00 sec)

    2. 执行导出命令

    [root@server-10 ~]# mysqldump -uroot -p -t -T /var/lib/mysql-files/  mydb customers  --fields-terminated-by=',' --fields-enclosed-by='"'

    3. 查看一下

    [root@server-10 ~]# ls -l /var/lib/mysql-files/
    total 4
    -rw-r--r-- 1 root  root    0 Aug 28 15:35 customers.sql    //会同步生成同名sql文件,内容为空
    -rw-rw-rw- 1 mysql mysql 458 Aug 28 15:35 customers.txt
    [root@server-10 ~]# cat /var/lib/mysql-files/customers.txt 
    "10001","Coyote Inc.","200 Maple Lane","Detroit","MI","44444","USA","Y Lee","ylee@coyote.com"
    "10002","Mouse House","333 Fromage Lane","Columbus","OH","43333","USA","Jerry Mouse",N
    "10003","Wascals","1 Sunny Place","Muncie","IN","42222","USA","Jim Jones","rabbit@wascally.com"
    "10004","Yosemite Place","829 Riverside Drive","Phoenix","AZ","88888","USA","Y Sam","sam@yosemite.com"
    "10005","E Fudd","4545 53rd Street","Chicago","IL","54545","USA","E Fudd",N

    4. 如果不想每个字段带冒号,则省略--fields-enclosed-by='"'即可

    [root@server-10 ~]# mysqldump -uroot -p -t -T /var/lib/mysql-files/  mydb customers  --fields-terminated-by=','

    5. 再查看一下输出的变化

    [root@server-10 ~]# ls -l /var/lib/mysql-files/
    total 4
    -rw-r--r-- 1 root  root    0 Aug 28 15:35 customers.sql
    -rw-rw-rw- 1 mysql mysql 372 Aug 28 15:36 customers.txt
    [root@server-10 ~]# cat /var/lib/mysql-files/customers.txt 
    10001,Coyote Inc.,200 Maple Lane,Detroit,MI,44444,USA,Y Lee,ylee@coyote.com
    10002,Mouse House,333 Fromage Lane,Columbus,OH,43333,USA,Jerry Mouse,N
    10003,Wascals,1 Sunny Place,Muncie,IN,42222,USA,Jim Jones,rabbit@wascally.com
    10004,Yosemite Place,829 Riverside Drive,Phoenix,AZ,88888,USA,Y Sam,sam@yosemite.com
    10005,E Fudd,4545 53rd Street,Chicago,IL,54545,USA,E Fudd,N

    结束.

  • 相关阅读:
    docker 简单使用
    apache 目录网站显示indexs
    MySQL索引失效的几种情况
    mysql 基本常用语句
    UNIX 版本
    B语言的发明者 Ken Thomson & C语言的发明者Dennis Ritchie
    My SQl 积累
    C# DGV多行选择
    C#中很模糊查询DGV中数据的两种方法
    网址
  • 原文地址:https://www.cnblogs.com/ding2016/p/9548528.html
Copyright © 2020-2023  润新知