mysql 查询后数据导出

mysqldump -u username -p database_name > output_file.sql

SELECT * INTO OUTFILE '/path/to/output/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name;

import pandas as pd
import mysql.connector

# 连接到MySQL数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="database_name"
)

# 创建游标
mycursor = mydb.cursor()

# 执行SQL查询
mycursor.execute("SELECT * FROM table_name")

# 获取查询结果
myresult = mycursor.fetchall()

# 将结果转换为DataFrame
df = pd.DataFrame(myresult, columns=[i[0] for i in mycursor.description])

# 导出为CSV文件
df.to_csv('/path/to/output/file.csv', index=False)

GRANT SELECT, LOCK TABLES, SHOW VIEW ON database_name.* TO 'username'@'localhost';

SELECT * INTO OUTFILE '/path/to/output/file.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name;