查询sql:
SELECT DB_CenterDB.`user`.Id, convert((DB_CenterDB.`user`.Nike)USING gbk), FROM_UNIXTIME(DB_CenterDB.`user`.RegTime,'%Y-%m-%d'), DB_CenterDB.`user`.AgentId, DB_CenterDB.`user`.Phone, DB_CenterDB.`user`.Mobile, convert((DB_WebDB.agent.`Name`)USING gbk) as agentname, DB_WebDB.agent.`Level` as agentlevel, DB_WebDB.admin.UserName as agentaccount , FROM_UNIXTIME(DB_CenterDB.`user`.LastLoginTime,'%Y-%m-%d') FROM DB_CenterDB.`user` LEFT JOIN DB_WebDB.agent ON DB_CenterDB.`user`.AgentId = DB_WebDB.agent.Id LEFT JOIN DB_WebDB.admin ON DB_WebDB.agent.AdminId = DB_WebDB.admin.Id WHERE DB_CenterDB.`user`.LastLoginTime <= 1557072000 ORDER BY DB_CenterDB.`user`.Id DESC into outfile '/var/lib/mysql-files/DB_Users.xls';
说明:
1、导出路径:
show variables like '%secure%';
导出的文件保存路径必须是上面的目录,否则会出现错误:MYSQL导入数据出现The MySQL server is running with the --secure-file-priv option so it cannot execute this statement。
2、convert 函数 将导出的字段内容指定的字符,否则导出后为乱码。
convert((DB_CenterDB.`user`.Nike)USING gbk)