当前位置：首页 > news >正文

MySQL 数据与表结构导出 Excel 技术文档

news 2025/8/21 6:49:43

MySQL 数据与表结构导出 Excel 技术文档

概述

本文档详细介绍如何将MySQL数据库中的表数据和表结构导出为Excel格式文件的各种方法，包括命令行工具、SQL语句和编程语言实现方案。

导出表数据到Excel

方法一：使用SELECT INTO OUTFILE

-- 基本语法
SELECT * FROM 表名
INTO OUTFILE '/路径/文件名.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';-- 示例（导出tf_new_qa_record表）
SELECT * FROM tf_new_qa_record
INTO OUTFILE '/tmp/tf_new_qa_record.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

特点：

需要MySQL服务器文件写入权限
直接在服务器端生成文件
可自定义字段分隔符、文本限定符等

方法二：使用MySQL命令行导出CSV

# 基本语法
mysql -h 主机名 -u 用户名 -p 数据库名 -e "SELECT * FROM 表名" | sed 's/\t/,/g' > 输出文件.csv# 示例（导出tf_new_qa_record表）
mysql -h rm-uf6e469p8276g7im7.mysql.rds.aliyuncs.com -usanshitest -p thfood_test -e "SELECT * FROM tf_new_qa_record" | sed 's/\t/,/g' > tf_new_qa_record.csv

特点：

不需要服务器文件写入权限
在客户端生成文件
简单快速，适合中小型数据表

方法三：使用mysqldump导出CSV

# 基本语法
mysqldump -h 主机名 -u 用户名 -p --tab=输出目录 --fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n' 数据库名 表名# 示例
mysqldump -h rm-uf6e469p8276g7im7.mysql.rds.aliyuncs.com -usanshitest -p --tab=/tmp --fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n' thfood_test tf_new_qa_record

特点：

生成.txt文件（可重命名为.csv）
需要指定输出目录的写入权限
适合需要保留完整数据格式的情况

方法四：使用编程语言(Python/PHP)

Python示例

import pandas as pd
import pymysql# 建立连接
connection = pymysql.connect(
host='rm-uf6e469p8276g7im7.mysql.rds.aliyuncs.com',
user='sanshitest',
password='your_password',
database='thfood_test'
)# 读取数据到DataFrame
df = pd.read_sql('SELECT * FROM tf_new_qa_record', con=connection)# 导出Excel
df.to_excel('tf_new_qa_record.xlsx', index=False)# 关闭连接
connection.close()

PHP示例

<?php
// 数据库连接
$conn = new mysqli('rm-uf6e469p8276g7im7.mysql.rds.aliyuncs.com', 'sanshitest', 'password', 'thfood_test');// 检查连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}// 查询数据
$result = $conn->query("SELECT * FROM tf_new_qa_record");// 创建PhpSpreadsheet对象
require 'vendor/autoload.php';
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();// 写入表头
$column = 'A';
foreach ($result->fetch_fields() as $field) {
$sheet->setCellValue($column.'1', $field->name);
$column++;
}// 写入数据
$row = 2;
while ($data = $result->fetch_assoc()) {
$column = 'A';
foreach ($data as $value) {
$sheet->setCellValue($column.$row, $value);
$column++;
}
$row++;
}// 保存Excel文件
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save('tf_new_qa_record.xlsx');// 关闭连接
$conn->close();
?>

导出表结构到Excel

方法一：使用INFORMATION_SCHEMA查询

-- 查询表结构
SELECT
COLUMN_NAME AS '列名',
COLUMN_TYPE AS '数据类型',
IS_NULLABLE AS '允许空',
COLUMN_DEFAULT AS '默认值',
COLUMN_COMMENT AS '注释'
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = '数据库名'
AND TABLE_NAME = '表名';-- 示例
SELECT
COLUMN_NAME AS '列名',
COLUMN_TYPE AS '数据类型',
IS_NULLABLE AS '允许空',
COLUMN_DEFAULT AS '默认值',
COLUMN_COMMENT AS '注释'
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = 'thfood_test'
AND TABLE_NAME = 'tf_new_qa_record'
INTO OUTFILE '/tmp/tf_new_qa_record_structure.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

方法二：使用SHOW CREATE TABLE

-- 获取建表语句
SHOW CREATE TABLE tf_new_qa_record;-- 可以导出到文件
SHOW CREATE TABLE tf_new_qa_record
INTO OUTFILE '/tmp/tf_new_qa_record_create_table.sql';

批量导出多表数据

使用Shell脚本批量导出

#!/bin/bash# 数据库连接信息
DB_HOST="rm-uf6e469p8276g7im7.mysql.rds.aliyuncs.com"
DB_USER="sanshitest"
DB_PASS="password"
DB_NAME="thfood_test"
OUTPUT_DIR="/tmp/mysql_exports"# 创建输出目录
mkdir -p $OUTPUT_DIR# 获取所有表名
TABLES=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -e "SHOW TABLES;" | grep -v "Tables_in")# 循环导出每个表
for TABLE in $TABLES; do
echo "导出表: $TABLE"# 导出数据
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -e "SELECT * FROM $TABLE" | sed 's/\t/,/g' > $OUTPUT_DIR/"${TABLE}_data.csv"# 导出结构
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -e "
SELECT
COLUMN_NAME AS 'column_name',
COLUMN_TYPE AS 'data_type',
IS_NULLABLE AS 'is_nullable',
COLUMN_DEFAULT AS 'default_value',
COLUMN_COMMENT AS 'comment'
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = '$DB_NAME'
AND TABLE_NAME = '$TABLE'
" | sed 's/\t/,/g' > $OUTPUT_DIR/"${TABLE}_structure.csv"
doneecho "导出完成，文件保存在: $OUTPUT_DIR"