MySQL encounters problems and solutions when exporting select statement results to excel files_MySQL

1. Export data externally

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

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

　-h: What follows is the host of the link

　-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 query results of the sql statement select * from edu_iclass_areas are 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

2) mysql connection and outputting query results to the database are executed separately

mysql -hxxx -uxx -pxx
select * from table into outfile 'xxx.txt';

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

For example:

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

　The execution effects of the above two methods are equivalent.

2. Problems encountered

1. Mac command to start mysql in the terminal

1) After installing the MySQL service (for installation steps, please refer to Series Experience 1). Open "System Preferences" and click the "MySQL" icon at the bottom.

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

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

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

mysql -u root -p 

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

Reason:

　 Mysql’s permissions are not released for localhost access.

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 mysql server from any host using mysql username myuser and use mypassword as password

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

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 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
flush privileges;

Just execute these two commands.

The above is the problem and solution encountered by mysql when exporting select statement results to excel files introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. . Thank you very much for your support of the website!