vlambda
2022-03-18

Mysql语句方法导出导入txt、csv、xls、xlsx2022.3.17

查看是否有权限

没有的话找到my.ini修改

show variables like '%secure%'

导出

SELECT * FROM `20220317` INTO OUTFILE "20220317.txt" SELECT * FROM `20220317` INTO OUTFILE "20220317.csv" SELECT * FROM `20220317` INTO OUTFILE "20220317.xlsx"

导入，

Error Code: 1300，把xlsx用笔记本打开，另存为utf-8格式

LOAD DATA INFILE '20220317.xlsx' INTO TABLE test.20220317

Mysql和几种格式在数据传输上接通了。

import pymysql as mysql    #填写SQL的账号密码端口号conn=mysql.connect( host='XXXXXX',port=3308,user='root',password='XXXXXX',database='test')cour = conn.cursor()#sql_str = "SELECT * FROM `20220317` INTO OUTFILE '20220317.xlsx'"
sql_str = "LOAD DATA INFILE '20220317.xlsx' INTO TABLE test.20220317"
cour.execute(sql_str)
conn.commit()cour.close()conn.close()