Problems and solutions encountered when mysql exports select statement results to excel files-PHP开发-php.cn

Problems and solutions encountered when mysql exports select statement results to excel files

高洛峰

Release： 2017-01-03 16:40:32

Original

1392 people have browsed it

1. Export data external

1) mysql connection + output the query results to a file. Execute in the command line (cmd command line of windows, terminal of mac)

mysql -hxx -uxx -pxx -e "query statement" db > file

Copy after login

　-h: followed by the linked host (host)

-u: What follows is the username

-p: What follows is the password

db: The database you want to query

File: The file you want to write, absolute path

For example:

The following is the query result of the sql statement select * from edu_iclass_areas The output is output to the file /Users/zhengcanrui/WORK/test/test.xls.

mysql -h127.0.0.1 -uroot -p123 -e "select * from edu_iclass_areas" test > /Users/zhengcanrui/WORK/test/test.xls

Copy after login

2) Mysql connection and output of query results to the database are executed separately

mysql -hxxx -uxx -pxx
select * from table into outfile &#39;xxx.txt&#39;;

Copy after login

The contents of the -h/-u/-p parameters are the same as above. xxx.txt is the file path and name to be output.

Such as:

-- 登录mysql
mysql -h127.0.0.1 -uroot -p123
-- 将查询结果输出到文件中
select * from edu_iclass_areas into outfile /Users/zhengcanrui/WORK/test/test.xls

Copy after login

The above two execution effects are equivalent.

2. Problems encountered

1. Mac starts the mysql command in the terminal

1) After installing the MySQL service ( For installation steps, please refer to series of experience 1). Open "System Preferences" and click the "MySQL" icon at the bottom.

mysql 导出select语句结果到excel文件遇到问题及解决方法

2) In the "MySQL" dialog box, click the "Start MySQL Service" button

mysql 导出select语句结果到excel文件遇到问题及解决方法

3) Click Applications in Finder's sidebar, and then in Utilities, double-click to launch the Terminal command.

4) Enter the command to add the MySQL path in the terminal:

PATH="$PATH":/usr/local/mysql/bin

Copy after login

5) The command to log in to MySQL in the terminal is as follows:

mysql -u root -p

Copy after login

mysql 导出select语句结果到excel文件遇到问题及解决方法

#2. Access denied for user 'mysql username'@'host or IP' (using password: YES)'

Cause:

MySQL's permissions have not been released to access localhost.

You can use the following command to release permissions (authorization):

First enter the mysql client command line.

Then enter:

1: You want to connect to the mysql server from any host using the mysql username myuser and use mypassword as the password

GRANT ALL PRIVILEGES ON *.* TO &#39;myuser&#39;@&#39;%&#39; IDENTIFIED BY &#39;mypassword&#39; WITH GRANT OPTION;
flush privileges;

Copy after login

2: You want to use the mysql username myuser to connect to the mysql server from the host with ip 192.168.1.3, and use mypassword as the password

GRANT ALL PRIVILEGES ON *.* TO &#39;myuser&#39;@&#39;192.168.1.3&#39; IDENTIFIED BY &#39;mypassword&#39; WITH GRANT OPTION;
flush privileges;

Copy after login

Just execute these two commands.

The above is the problem and solution encountered by mysql exporting select statement results to excel files introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message. , the editor will reply to everyone in time. I would also like to thank you all for your support of the PHP Chinese website!

For more mysql export select statement results to excel files and related articles about problems and solutions, please pay attention to the PHP Chinese website!