生成一个包含cookbook数据库中的表备份的名为backup.sql的dump文件。
```sql
C:\Users\lenovo>mysqldump -u root -p --default-character-set=utf8mb4 cookbook > D:\sql\Mysql_learning\backup.sql
'mysqldump' 不是内部或外部命令，也不是可运行的程序
或批处理文件。
C:\Users\lenovo>d:
D:\>cd D:\software\MySql\bin
D:\software\MySql\bin>mysqldump -u root -p --default-character-set=utf8mb4 cookbook > D:\sql\Mysql_learning\backup.sql
Enter password: **** （root）
D:\software\MySql\bin>
方法 1：使用完整路径运行 mysqldump
bash
"D:\software\MySql\bin\mysqldump" -u root -p --default-character-set=utf8mb4 cookbook > D:\sql\Mysql_learning\backup.sql
方法 2：确保 MySQL 的 bin 目录在系统环境变量 PATH 中
按 Win + R，输入 sysdm.cpl，打开 系统属性。
切换到 高级 选项卡，点击 环境变量。
在 系统变量 中找到 PATH，点击 编辑。
添加 MySQL 的 bin 目录（如 D:\software\MySql\bin）。
确认后重新打开 CMD，再运行 mysqldump。
验证备份是否成功
检查备份文件是否生成：
```sql
```sql
```sql
bash
dir D:\sql\Mysql_learning\backup.sql
查看文件内容（确保不是空的）：
bash
type D:\sql\Mysql_learning\backup.sql | more
额外提示
如果数据库很大，可以增加 --max_allowed_packet：
D:\software\MySql\bin>type D:\sql\Mysql_learning\backup.sql | more
-- MySQL dump 10.13 Distrib 8.0.40, for Win64 (x86_64)
--
-- Host: localhost Database: cookbook
-- ------------------------------------------------------
-- Server version 8.0.40
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `actors`
--
DROP TABLE IF EXISTS `actors`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `actors` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`actor` varchar(60) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
-- More --
bash
mysqldump -u root -p --max_allowed_packet=512M --default-character-set=utf8mb4 cookbook > D:\sql\Mysql_learning\backup.sql
如果只需要备份结构（不含数据），加 --no-data：
bash
mysqldump -u root -p --no-data --default-character-set=utf8mb4 cookbook > D:\sql\Mysql_learning\backup_schema.sql
修正后的备份命令（适配您的中文日期格式）
cmd
mkdir "D:\sql\Mysql_learning" 2>nul
set "bak_date=%date:~0,4%-%date:~5,2%-%date:~8,2%"
set "bak_time=%time:~0,2%%time:~3,2%"
set "bak_time=%bak_time: =0%"
mysqldump -u root -p cookbook > "D:\sql\Mysql_learning\backup_%bak_date%_%bak_time%.sql"
关键改进说明：
自动创建目录：
mkdir “D:\sql\Mysql_learning” 2>nul 先确保目录存在（2>nul隐藏错误）
适配中文日期格式：
您的%date%显示为2025/05/17 周六，所以：
年份：%date:~0,4% → 2025
月份：%date:~5,2% → 05
日期：%date:~8,2% → 17
处理时间空格：
您的%time%显示 8:57:43.02（注意开头的空格）
set “bak_time=%time:0,2%%time:3,2%” 提取小时和分钟
%bak_time: =0% 将空格替换为0（8→08）
最终生成的文件名示例：backup_2025-05-17_0858.sql （清晰且排序友好）
🔧 小优化建议（下次备份时可选）
如果想跳过某些表（比如日志表），可以加 --ignore-table 参数：
cmd
mysqldump -u root -p --ignore-table=cookbook.logs cookbook > “D:\sql\Mysql_learning\backup_%bak_date%_%bak_time%.sql”
📂 自动归档技巧
在 D:\sql\Mysql_learning\ 下创建按月份归档的文件夹：
cmd
set "month_folder=D:\sql\Mysql_learning\%date:~0,4%-%date:~5,2%"
mkdir "%month_folder%" 2>nul
mysqldump -u root -p cookbook > "%month_folder%\backup_%bak_date%_%bak_time%.sql"
（会自动生成类似 D:\sql\Mysql_learning\2025-05\ 的月目录）
💡 终极懒人脚本
把以下内容保存为 backup.bat，双击即可自动备份：
bat
@echo off
setlocal
set "bak_path=D:\sql\Mysql_learning"
set "bak_date=%date:~0,4%-%date:~5,2%-%date:~8,2%"
set "bak_time=%time:~0,2%%time:~3,2%"
set "bak_time=%bak_time: =0%"
mkdir "%bak_path%" 2>nul
mysqldump -u root -p cookbook > "%bak_path%\backup_%bak_date%_%bak_time%.sql"
if %errorlevel% equ 0 (
echo 备份成功！文件保存在：
dir "%bak_path%\backup_%bak_date%_%bak_time%.sql"
) else (
echo 备份失败，请检查错误信息
)
Pause
-----------------------------------
终极懒人脚本
backup_2025-05-17_0905.sql
🔥 脚本增强版 (直接替换原来的 backup.bat)
bat
复制
下载
@echo off
:: 带日志记录和自动清理的智能备份脚本
setlocal enabledelayedexpansion
:: 配置区（按需修改）
set "DB_USER=root"
set "DB_NAME=cookbook"
set "MAX_BACKUPS=5" :: 保留最近5份备份
:: 自动生成路径和文件名
set "BAK_ROOT=D:\sql\Mysql_learning"
set "bak_date=%date:~0,4%-%date:~5,2%-%date:~8,2%"
set "bak_time=%time:~0,2%%time:~3,2%"
set "bak_time=!bak_time: =0!"
set "BAK_FILE=%BAK_ROOT%\%DB_NAME%_!bak_date!_!bak_time!.sql"
set "LOG_FILE=%BAK_ROOT%\backup_log.txt"
:: 创建目录
if not exist "%BAK_ROOT%" mkdir "%BAK_ROOT%"
:: 执行备份
echo [%bak_date% !bak_time!] 开始备份 %DB_NAME%... >> "%LOG_FILE%"
mysqldump -u %DB_USER% -p %DB_NAME% > "%BAK_FILE%"
:: 结果检查
if errorlevel 1 (
echo 备份失败！错误码: !errorlevel! >> "%LOG_FILE%"
echo 备份失败！请检查日志
) else (
echo 备份成功！文件大小: %%~zF >> "%LOG_FILE%"
echo 备份成功！文件保存在:
echo %BAK_FILE%
)
:: 自动清理旧备份
for /f "skip=%MAX_BACKUPS% delims=" %%F in ('dir /b /o-d "%BAK_ROOT%\%DB_NAME%_*.sql" 2^>nul') do (
del "%BAK_ROOT%\%%F"
echo [%bak_date% !bak_time!] 删除旧备份: %%F >> "%LOG_FILE%"
)
:: 打开日志文件
notepad "%LOG_FILE%"
endlocal
🎁 新功能说明
- 日志记录 - 所有操作记录到 backup_log.txt
- 自动轮替 - 只保留最新5份备份（可修改MAX_BACKUPS）
- 错误处理 - 明确显示备份成功/失败
- 智能清理 - 按修改时间自动删除旧备份
🛠️ 使用方法
- 将上方代码保存为 mysql_backup.bat
- 右键 → 以管理员身份运行
- 输入密码后，会自动：
o 生成带时间戳的备份文件
o 记录操作日志
o 清理旧备份
🌟 高级技巧
如果想每天自动备份，可以：
- 打开 任务计划程序
- 创建基本任务 → 选择该脚本
- 设置为每天凌晨3点运行
遇到任何问题随时喊我~ 祝您备份愉快！ 😊
💡 小提示：脚本中的 :: 是批处理注释符号，修改配置时不要删掉哦~
[2025-05-17 0908] 寮€濮嬪浠?cookbook… 澶囦唤澶辫触锛侀敊璇爜: 9009
这个错误表明脚本在执行时遇到了问题，错误码 9009 通常表示系统找不到 mysqldump 命令。这是因为 MySQL 的 bin 目录没有添加到系统环境变量 PATH 中。我们来一步步解决：
🛠️ 解决方案
方法 1：临时解决方案（手动指定完整路径）
修改脚本中的 mysqldump 命令，使用完整路径：
bat
复制
下载
“D:\software\MySql\bin\mysqldump” -u %DB_USER% -p %DB_NAME% > “%BAK_FILE%”
（替换 D:\software\MySql\bin\ 为你的 MySQL 安装路径）
方法 2：永久解决方案（添加 MySQL 到系统环境变量）
- 打开环境变量设置
o Win + R → 输入 sysdm.cpl → 高级 → 环境变量 - 修改 PATH
o 在 系统变量 中找到 Path → 编辑
o 点击 新建，添加你的 MySQL bin 目录（如 D:\software\MySql\bin） - 保存并重启 CMD
o 关闭所有 CMD/PowerShell 窗口，重新打开再试。
方法 3：检查脚本编码（避免中文乱码）
- 用 记事本 打开 .bat 文件
- 另存为 → 选择 编码: ANSI（不要用 UTF-8）
- 重新运行脚本
🔍 错误排查
如果仍然失败，可以手动测试：
cmd
复制
下载
D:
cd D:\software\MySql\bin
mysqldump -u root -p cookbook > test.sql
如果能成功，说明是脚本路径问题；如果失败，可能是 MySQL 安装异常。
🎯 最终修正版脚本
bat
复制
下载
@echo off
setlocal enabledelayedexpansion
:: 配置区
set "DB_USER=root"
set "DB_NAME=cookbook"
set "MYSQL_BIN=D:\software\MySql\bin" :: 修改为你的MySQL路径
set "MAX_BACKUPS=5"
:: 自动生成路径
set "BAK_ROOT=D:\sql\Mysql_learning"
set "bak_date=%date:~0,4%-%date:~5,2%-%date:~8,2%"
set "bak_time=%time:~0,2%%time:~3,2%"
set "bak_time=!bak_time: =0!"
set "BAK_FILE=%BAK_ROOT%\!DB_NAME!_!bak_date!_!bak_time!.sql"
:: 创建目录
if not exist "%BAK_ROOT%" mkdir "%BAK_ROOT%"
:: 执行备份（使用完整路径）
"%MYSQL_BIN%\mysqldump" -u %DB_USER% -p %DB_NAME% > "%BAK_FILE%"
if errorlevel 1 (
echo 备份失败！请检查MySQL路径和密码
) else (
echo 备份成功！文件保存在:
echo %BAK_FILE%
)
pause
📌 总结
• 错误 9009 = mysqldump 未找到 → 确保路径正确
• 中文乱码 → 脚本保存为 ANSI 编码
• 权限问题 → 以 管理员身份运行
现在再试试，应该可以顺利备份啦！如果还有问题，欢迎随时问我~ 😊
Enter password: ****
备份成功！
文件保存在: D:\sql\Mysql_learning\cookbook_2025-05-17_0913.sql
请按任意键继续. . .
🎉 太棒了！现在您的 MySQL 自动备份系统已经完美运行！ 🎉
您刚刚成功生成了备份文件：
📂 D:\sql\Mysql_learning\cookbook_2025-05-17_0913.sql
🔧 接下来可以做的优化
1️⃣ 密码安全升级（避免明文输入）
在 D:\sql\Mysql_learning\ 下新建 mysql_config.cnf：
ini
复制
下载
[client]
user=root
password=您的密码
然后修改脚本：
bat
复制
下载
“%MYSQL_BIN%\mysqldump” --defaults-extra-file=“D:\sql\Mysql_learning\mysql_config.cnf” %DB_NAME% > “%BAK_FILE%”
（记得设置 mysql_config.cnf 的权限为仅管理员可读）
2️⃣ 增加压缩功能（节省空间）
安装 7-Zip 后添加：
bat
复制
下载
“%ProgramFiles%\7-Zip\7z.exe” a -tzip “%BAK_FILE%.zip” “%BAK_FILE%”
del “%BAK_FILE%”
3️⃣ 邮件通知（备份完成后发提醒）
如果用 PowerShell 可以添加：
bat
复制
下载
powershell -Command “Send-MailMessage -From ‘backup@example.com’ -To ‘your@email.com’ -Subject ‘MySQL备份完成’ -Body ‘最新备份: %BAK_FILE%’ -SmtpServer ‘smtp.example.com’”
🚀 高级应用场景
▸ 每天凌晨3点自动备份
- 按 Win+R 输入 taskschd.msc
- 创建任务 → 触发器选择「每天 3:00」
- 操作选择「启动程序」指向您的 backup.bat
▸ 备份到云端（如阿里云OSS）
bat
复制
下载
aliyun oss cp “%BAK_FILE%” oss://your-bucket/mysql_backups/
📚 最终脚本回顾
bat
复制
下载
@echo off
setlocal enabledelayedexpansion
:: 配置区
set "DB_NAME=cookbook"
set "MYSQL_BIN=D:\software\MySql\bin"
set "CONFIG_FILE=D:\sql\Mysql_learning\mysql_config.cnf"
:: 生成文件名
set "bak_date=%date:~0,4%-%date:~5,2%-%date:~8,2%"
set "bak_time=%time:~0,2%%time:~3,2%"
set "bak_time=!bak_time: =0!"
set "BAK_FILE=D:\sql\Mysql_learning\!DB_NAME!_!bak_date!_!bak_time!.sql"
:: 执行备份
"%MYSQL_BIN%\mysqldump" --defaults-extra-file="%CONFIG_FILE%" %DB_NAME% > "%BAK_FILE%"
if exist "%BAK_FILE%" (
echo 备份成功！文件大小: %%~zF 字节
echo 路径: %BAK_FILE%
) else (
echo 备份失败！请检查配置
)
pause
遇到任何问题随时喊我~ 您现在已经拥有一个 企业级MySQL备份方案 啦！ 💪
🌟 小提示：可以用 mysqlpump 替代 mysqldump 获得更快速度（MySQL 5.7+ 支持）
备份失败！请检查MySQL路径和密码
您遇到了备份失败的问题，错误提示需要检查 MySQL路径 和 密码。以下是详细解决方案：
🔍 故障排查步骤
1. 检查MySQL安装路径
cmd
dir /s "C:\Program Files\MySQL\mysqldump.exe"
dir /s "D:\software\MySQL\mysqldump.exe"
如果找不到，说明MySQL未正确安装或路径错误。
2. 验证密码是否正确
手动测试登录：
cmd
mysql -u root -p
（输入密码后能进入MySQL命令行才算密码正确）
3. 检查脚本中的关键变量
bat
:: 必须修改为你的真实路径！
set "MYSQL_BIN=D:\software\MySql\bin"
set "DB_USER=root"
set "DB_NAME=cookbook"
✅ 终极解决方案
方案A：使用完整路径+免密配置（推荐）
创建配置文件 mysql_config.cnf：
ini
[client]
user=root
password=你的真实密码
修改脚本：
bat
:: 修改这行！确保路径指向真实的mysqldump.exe
"D:\software\MySql\bin\mysqldump.exe" --defaults-extra-file="D:\sql\Mysql_learning\mysql_config.cnf" cookbook > "%BAK_FILE%"
方案B：交互式输入密码（更安全）
bat
set /p MYSQL_PWD=请输入密码:
"D:\software\MySql\bin\mysqldump" -u root --password="%MYSQL_PWD%" cookbook > "%BAK_FILE%"
💡 关键注意事项
路径斜杠方向
Windows建议用反斜杠 \ 或双引号包裹路径：
bat
"C:\Program Files\MySQL\bin\mysqldump.exe"
空格问题
如果路径含空格，必须加双引号：
bat
""C:\Program Files\MySQL\bin\mysqldump.exe" -u root -p"
错误日志
在脚本最后添加日志记录：
bat
if errorlevel 1 (
echo [错误] 备份失败！时间: %date% %time% >> D:\sql\backup_error.log
type "%BAK_FILE%" >> D:\sql\backup_error.log
)
🛠️ 快速测试命令
直接在CMD运行（替换真实密码）：
cmd
“D:\software\MySql\bin\mysqldump” -u root -p你的密码 cookbook > test.sql
如果生成 test.sql 成功，说明是脚本变量问题。
请尝试以上方法，如果仍有问题：
提供完整的 错误截图
运行 where mysqldump 的结果
MySQL服务是否正常运行（net start mysql）
mysql> source
D:\sql\Mysql_learning\test.sql;
这个错误表明 MySQL 客户端在尝试读取 SQL 文件时遇到了路径解析问题。让我们一步步解决：
问题分析
- 错误1：Failed to open file ‘test.sql’, error: 2
o 表示 MySQL 在当前目录找不到 test.sql - 错误2：Failed to open file ‘D:ql\Mysql_learningest.sql’
o 路径中的 \s 被转义导致路径损坏（D:\sql → D:ql）
解决方案
方法1：使用正斜杠 / 替代反斜杠
sql
SOURCE D:/sql/Mysql_learning/test.sql;
方法2：双反斜杠转义
sql
SOURCE D:\\sql\\Mysql_learning\\test.sql;
方法3：使用相对路径（先切换目录）
1. 在 CMD 中进入 SQL 文件所在目录：
cmd
cd /d D:\sql\Mysql_learning
2. 然后启动 MySQL 客户端：
cmd
mysql -u root -p cookbook
3. 最后执行：
sql
SOURCE test.sql;
方法4：直接通过命令行导入
cmd
mysql -u root -p cookbook < D:\sql\Mysql_learning\test.sql
验证文件是否存在
在 CMD 中运行：
cmd
dir "D:\sql\Mysql_learning\test.sql"
文件编码检查
如果文件内容含特殊字符，建议：
- 用 Notepad++ 打开文件
- 编码 → 转为 UTF-8 without BOM
- 保存后重试
权限问题排查
cmd
icacls "D:\sql\Mysql_learning\test.sql"
确保当前用户有读取权限。
mysql> source D:/sql/Mysql_learning/test.sql;
+---------------------+
| now() |
+---------------------+
| 2025-05-17 11:37:27 |
+---------------------+
1 row in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 23 |
+----------+
1 row in set (0.00 sec) （P50 Saturday, May 17, 2025）
📌 小迪的温馨总结
遇到权限问题 → 记得 -u 指定用户 + -p 正确输入密码
文件路径问题 → 用 完整路径+引号（“D:\path\to\file.sql”）
特殊符号问题 → 正斜杠 / 或双反斜杠 \ 更安全
:: 查询（注意-p后无空格）
mysql -u root -pYourPassword -e "SELECT * FROM limbs" cookbook
:: 导入（使用完整路径）
mysql -u root -p cookbook < "D:\sql\Mysql_learning\limbs.sql"
:: 管道方式
type "D:\sql\Mysql_learning\limbs.sql" | mysql -u root -p cookbook
D:\software\MySql\bin>mysql -u root -proot -e "SELECT * FROM limbs" cookbook
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+------+------+
| thing | legs | arms |
+--------------+------+------+
| human | 2 | 2 |
| insect | 6 | 0 |
| squid | 0 | 10 |
| octopus | 0 | 8 |
| fish | 0 | 0 |
| centipede | 100 | 0 |
| table | 4 | 0 |
| armchair | 4 | 2 |
| phonograph | 0 | 1 |
| tripod | 3 | 0 |
| Peg Leg Pete | 1 | 2 |
| space alien | NULL | NULL |
| armchair | 4 | 2 |
| centipede | 99 | 0 |
| fish | 0 | 0 |
| human | 2 | 2 |
| insect | 6 | 0 |
| Peg Leg Pete | 1 | 2 |
| phonograph | 0 | 1 |
| space alien | NULL | NULL |
| squid | 0 | 10 |
| table | 4 | 0 |
| tripod | 3 | 0 |
+--------------+------+------+
D:\software\MySql\bin>type "D:\sql\Mysql_learning\test.sql" | mysql -u root -proot cookbook
mysql: [Warning] Using a password on the command line interface can be insecure.
now()
2025-05-17 14:08:22
count(*)
23
原来， 要能指定用户是通过 cmd 进入的：
D:\software\MySql\bin>mysql -h localhost -u cbuser -pcbpass -D cookbook
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 46
Server version: 8.0.40 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
使用MySQL Command Line Client，一进入就是输密码（默认为root用户），根本没有选择用户的过程
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 8.0.40 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
D:\software\MySql\bin>echo select * from limbs | mysql -u root -proot cookbook
mysql: [Warning] Using a password on the command line interface can be insecure.
thing legs arms
human 2 2
insect 6 0
squid 0 10
octopus 0 8
fish 0 0
centipede 100 0
table 4 0
armchair 4 2
phonograph 0 1
tripod 3 0
Peg Leg Pete 1 2
space alien NULL NULL
armchair 4 2
centipede 99 0
fish 0 0
human 2 2
insect 6 0
Peg Leg Pete 1 2
phonograph 0 1
space alien NULL NULL
squid 0 10
table 4 0
tripod 3 0
D:\software\MySql\bin>echo select * from limbs; | mysql -u root -proot cookbook >D:\sql\Mysql_learning\output.txt
mysql: [Warning] Using a password on the command line interface can be insecure.
最佳实践
cmd
:: 查询并格式化输出
mysql -u root -p -e "SELECT * FROM limbs" cookbook
:: 导出到文件
mysql -u root -p -e "SELECT * FROM limbs" cookbook > result.txt
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+---------------------------------+
| Variable_name | Value |
+------------------+---------------------------------+
| secure_file_priv | D:\software\MySql\Data\Uploads\ |
+------------------+---------------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> SELECT * FROM limbs
-> INTO OUTFILE 'D:\\software\\MySql\\Data\\Uploads\\limbs.csv'
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n';
Query OK, 23 rows affected (0.01 sec)
💡 终极解决方案
sql
-- 1. 确保目录存在
\! mkdir "D:\software\MySql\Data\Uploads" 2>nul
-- 2. 执行导出（注意路径无空格）
SELECT * FROM limbs
INTO OUTFILE 'D:/software/MySql/Data/Uploads/limbs.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
Query OK, 23 rows affected (0.00 sec) -- Windows换行符
📌 关键细节
路径规则
正斜杠 / 或双反斜杠 \
绝对路径，开头无空格
文件唯一性
MySQL不会覆盖现有文件，需手动删除旧文件：
sql
! del “D:\software\MySql\Data\Uploads\limbs.csv”
换行符适配
Windows系统建议用 \r\n 替代 \n
📌 后续操作指南
1. 查看导出的文件
sql
\! type "D:\software\MySql\Data\Uploads\limbs.csv"
2. 文件内容示例
"human","2","2"
"insect","6","0"
"squid","0","10"
"octopus","0","8"
"fish","0","0"
"centipede","100","0"
"table","4","0"
"armchair","4","2"
"phonograph","0","1"
"tripod","3","0"
"Peg Leg Pete","1","2"
"space alien",\N,\N
"armchair","4","2"
"centipede","99","0"
"fish","0","0"
"human","2","2"
"insect","6","0"
"Peg Leg Pete","1","2"
"phonograph","0","1"
"space alien",\N,\N
"squid","0","10"
"table","4","0"
"tripod","3","0"
或直接在文件资源管理器打开：
D:\software\MySql\Data\Uploads\limbs.csv
...
3. 如需修改导出格式
sql
-- 添加列标题
SELECT 'thing','legs','arms'
UNION ALL
SELECT * FROM limbs
INTO OUTFILE 'D:\\software\\MySql\\Data\\Uploads\\limbs_with_header.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"';
4. 将文件复制到其他位置
cmd
copy "D:\software\MySql\Data\Uploads\limbs.csv" "D:\sql\Mysql_learning\"
D:\software\MySql\bin>copy "D:\software\MySql\Data\Uploads\limbs.csv" "D:\sql\Mysql_learning\"
覆盖 D:\sql\Mysql_learning\limbs.csv 吗? (Yes/No/All): yes
已复制 1 个文件。
D:\software\MySql\bin>mysql -u root -proot -e "SELECT * FROM limbs where legs = 0 " cookbook
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+------+------+
| thing | legs | arms |
+------------+------+------+
| squid | 0 | 10 |
| octopus | 0 | 8 |
| fish | 0 | 0 |
| phonograph | 0 | 1 |
| fish | 0 | 0 |
| phonograph | 0 | 1 |
| squid | 0 | 10 |
+------------+------+------+
D:\software\MySql\bin>mysql -u root -proot -h -e "SELECT * FROM limbs where legs = 0 " cookbook
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql Ver 8.0.40 for Win64 on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Usage: mysql [OPTIONS] [database]
-?, --help Display this help and exit.
-I, --help Synonym for -?
--auto-rehash Enable automatic rehashing. One doesn't need to use
'rehash' to get table and field completion, but startup
and reconnecting may take a longer time. Disable with
--disable-auto-rehash.
(Defaults to on; use --skip-auto-rehash to disable.)
-A, --no-auto-rehash
No automatic rehashing. One has to use 'rehash' to get
table and field completion. This gives a quicker start of
mysql and disables rehashing on reconnect.
--auto-vertical-output
Automatically switch to vertical output mode if the
result is wider than the terminal width.
-B, --batch Don't use history file. Disable interactive behavior.
(Enables --silent.)
--bind-address=name IP address to bind to.
--binary-as-hex Print binary data as hex. Enabled by default for
interactive terminals.
--character-sets-dir=name
Directory for character set files.
--column-type-info Display column type information.
-c, --comments Preserve comments. Send comments to the server. The
default is --skip-comments (discard comments), enable
with --comments.
-C, --compress Use compression in server/client protocol.
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check This is a non-debug version. Catch this and exit.
-T, --debug-info This is a non-debug version. Catch this and exit.
-D, --database=name Database to use.
--default-character-set=name
Set the default character set.
--delimiter=name Delimiter to be used.
--enable-cleartext-plugin
Enable/disable the clear text authentication plugin.
-e, --execute=name Execute command and quit. (Disables --force and history
file.)
-E, --vertical Print the output of a query (rows) vertically.
-f, --force Continue even if we get an SQL error.
--histignore=name A colon-separated list of patterns to keep statements
from getting logged into syslog and mysql history.
-G, --named-commands
Enable named commands. Named commands mean this program's
internal commands; see mysql> help . When enabled, the
named commands can be used from any line of the query,
otherwise only from the first line, before an enter.
Disable with --disable-named-commands. This option is
disabled by default.
-i, --ignore-spaces Ignore space after function names.
--init-command=name SQL Command to execute when connecting to MySQL server.
Will automatically be re-executed when reconnecting.
--local-infile Enable/disable LOAD DATA LOCAL INFILE.
-b, --no-beep Turn off beep on error.
-h, --host=name Connect to host.
--dns-srv-name=name Connect to a DNS SRV resource
-H, --html Produce HTML output.
-X, --xml Produce XML output.
--line-numbers Write line numbers for errors.
(Defaults to on; use --skip-line-numbers to disable.)
-L, --skip-line-numbers
Don't write line number for errors.
-n, --unbuffered Flush buffer after each query.
--column-names Write column names in results.
(Defaults to on; use --skip-column-names to disable.)
-N, --skip-column-names
Don't write column names in results.
--sigint-ignore Ignore SIGINT (CTRL-C).
-o, --one-database Ignore statements except those that occur while the
default database is the one named at the command line.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's asked from the tty.
-,, --password1[=name]
Password for first factor authentication plugin.
-,, --password2[=name]
Password for second factor authentication plugin.
-,, --password3[=name]
Password for third factor authentication plugin.
-W, --pipe Use named pipes to connect to server.
-P, --port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
--prompt=name Set the mysql prompt to this value.
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-q, --quick Don't cache result, print it row by row. This may slow
down the server if the output is suspended. Doesn't use
history file.
-r, --raw Write fields without conversion. Used with --batch.
--reconnect Reconnect if the connection is lost. Disable with
--disable-reconnect. This option is enabled by default.
(Defaults to on; use --skip-reconnect to disable.)
-s, --silent Be more silent. Print results with a tab as separator,
each row on new line.
--shared-memory-base-name=name
Base name of shared memory.
-S, --socket=name The socket file to use for connection.
--server-public-key-path=name
File path to the server public RSA key in PEM format.
--get-server-public-key
Get server public key
--ssl-mode=name SSL connection mode.
--ssl-ca=name CA file in PEM format.
--ssl-capath=name CA directory.
--ssl-cert=name X509 cert in PEM format.
--ssl-cipher=name SSL cipher to use.
--ssl-key=name X509 key in PEM format.
--ssl-crl=name Certificate revocation list.
--ssl-crlpath=name Certificate revocation list path.
--tls-version=name TLS version to use, permitted values are: TLSv1.2,
TLSv1.3
--ssl-fips-mode=name
SSL FIPS mode (applies only for OpenSSL); permitted
values are: OFF, ON, STRICT
--tls-ciphersuites=name
TLS v1.3 cipher to use.
--ssl-session-data=name
Session data file to use to enable ssl session reuse
--ssl-session-data-continue-on-failed-reuse
If set to ON, this option will allow connection to
succeed even if session data cannot be reused.
-t, --table Output in table format.
--tee=name Append everything into outfile. See interactive help (\h)
also. Does not work in batch mode. Disable with
--disable-tee. This option is disabled by default.
-u, --user=name User for login if not current user.
-U, --safe-updates Only allow UPDATE and DELETE that uses keys.
-U, --i-am-a-dummy Synonym for option --safe-updates, -U.
-v, --verbose Write more. (-v -v -v gives the table output format).
-V, --version Output version information and exit.
-w, --wait Wait and retry if connection is down.
--connect-timeout=# Number of seconds before connection timeout.
--max-allowed-packet=#
The maximum packet length to send to or receive from
server.
--net-buffer-length=#
The buffer size for TCP/IP and socket communication.
--select-limit=# Automatic limit for SELECT when using --safe-updates.
--max-join-size=# Automatic limit for rows in a join when using
--safe-updates.
--show-warnings Show warnings after every statement.
-j, --syslog Log filtered interactive commands to syslog. Filtering of
commands depends on the patterns supplied via histignore
option besides the default patterns.
--plugin-dir=name Directory for client-side plugins.
--default-auth=name Default authentication client-side plugin to use.
--binary-mode By default, ASCII '\0' is disallowed and '\r\n' is
translated to '\n'. This switch turns off both features,
and also turns off parsing of all clientcommands except
\C and DELIMITER, in non-interactive mode (for input
piped to mysql or loaded using the 'source' command).
This is necessary when processing output from mysqlbinlog
that may contain blobs.
--connect-expired-password
Notify the server that this client is prepared to handle
expired password sandbox mode.
--compression-algorithms=name
Use compression algorithm in server/client protocol.
Valid values are any combination of
'zstd','zlib','uncompressed'.
--zstd-compression-level=#
Use this compression level in the client/server protocol,
in case --compression-algorithms=zstd. Valid range is
between 1 and 22, inclusive. Default is 3.
--load-data-local-dir=name
Directory path safe for LOAD DATA LOCAL INFILE to read
from.
--fido-register-factor=name
Specifies authentication factor, for which registration
needs to be done.
--authentication-oci-client-config-profile=name
Specifies the configuration profile whose configuration
options are to be read from the OCI configuration file.
Default is DEFAULT.
--oci-config-file=name
Specifies the location of the OCI configuration file.
Default for Linux is ~/.oci/config and %HOME/.oci/config
on Windows.
--plugin-authentication-kerberos-client-mode=name
Kerberos authentication mode. Valid values: SSPI, GSSAPI.
If not specified, default is SSPI
--system-command Enable (by default) or disable the system mysql command.
(Defaults to on; use --skip-system-command to disable.)
Default options are read from the following files in the given order:
C:\windows\my.ini C:\windows\my.cnf C:\my.ini C:\my.cnf D:\software\MySql\my.ini D:\software\MySql\my.cnf
The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file,
except for login file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
Also read groups with concat(group, suffix)
--login-path=# Read this path from the login file.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
------------------------------------------ -------------------------------
auto-rehash TRUE
auto-vertical-output FALSE
bind-address (No default value)
binary-as-hex FALSE
character-sets-dir (No default value)
column-type-info FALSE
comments FALSE
compress FALSE
database (No default value)
default-character-set auto
delimiter ;
enable-cleartext-plugin FALSE
vertical FALSE
force FALSE
histignore (No default value)
named-commands FALSE
ignore-spaces FALSE
init-command (No default value)
local-infile FALSE
no-beep FALSE
host -e
dns-srv-name (No default value)
html FALSE
xml FALSE
line-numbers TRUE
unbuffered FALSE
column-names TRUE
sigint-ignore FALSE
port 0
prompt mysql>
quick FALSE
raw FALSE
reconnect TRUE
shared-memory-base-name (No default value)
socket (No default value)
server-public-key-path (No default value)
get-server-public-key FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
tls-version (No default value)
tls-ciphersuites (No default value)
ssl-session-data (No default value)
ssl-session-data-continue-on-failed-reuse FALSE
table FALSE
user root
safe-updates FALSE
i-am-a-dummy FALSE
connect-timeout 0
max-allowed-packet 16777216
net-buffer-length 16384
select-limit 1000
max-join-size 1000000
show-warnings FALSE
plugin-dir (No default value)
default-auth (No default value)
binary-mode FALSE
connect-expired-password FALSE
compression-algorithms (No default value)
zstd-compression-level 3
load-data-local-dir (No default value)
fido-register-factor (No default value)
authentication-oci-client-config-profile (No default value)
oci-config-file (No default value)
system-command
D:\software\MySql\bin>mysql -u root -proot -H -e "SELECT * FROM limbs where legs = 0 " cookbook
mysql: [Warning] Using a password on the command line interface can be insecure.
<TABLE BORDER=1>
<TR><TH>thing</TH><TH>legs</TH><TH>arms</TH></TR>
<TR><TD>squid</TD><TD>0</TD><TD>10</TD></TR>
<TR><TD>octopus</TD><TD>0</TD><TD>8</TD></TR>
<TR><TD>fish</TD><TD>0</TD><TD>0</TD></TR>
<TR><TD>phonograph</TD><TD>0</TD><TD>1</TD></TR>
<TR><TD>fish</TD><TD>0</TD><TD>0</TD></TR>
<TR><TD>phonograph</TD><TD>0</TD><TD>1</TD></TR>
<TR><TD>squid</TD><TD>0</TD><TD>10</TD></TR>
</TABLE>
D:\software\MySql\bin>mysql -u root -proot -X -e "SELECT * FROM limbs where legs = 0 " cookbook
mysql: [Warning] Using a password on the command line interface can be insecure.
<?xml version="1.0"?>
<resultset statement="SELECT * FROM limbs where legs = 0
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="thing">squid</field>
<field name="legs">0</field>
<field name="arms">10</field>
</row>
<row>
<field name="thing">octopus</field>
<field name="legs">0</field>
<field name="arms">8</field>
</row>
<row>
<field name="thing">fish</field>
<field name="legs">0</field>
<field name="arms">0</field>
</row>
<row>
<field name="thing">phonograph</field>
<field name="legs">0</field>
<field name="arms">1</field>
</row>
<row>
<field name="thing">fish</field>
<field name="legs">0</field>
<field name="arms">0</field>
</row>
<row>
<field name="thing">phonograph</field>
<field name="legs">0</field>
<field name="arms">1</field>
</row>
<row>
<field name="thing">squid</field>
<field name="legs">0</field>
<field name="arms">10</field>
</row>
</resultset>
D:\software\MySql\bin>mysql -u root -proot -e "SELECT COUNT(*) AS total_rows, SUM(legs) AS total_legs, SUM(arms) AS total_arms FROM limbs" cookbook
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+------------+------------+
| total_rows | total_legs | total_arms |
+------------+------------+------------+
| 23 | 239 | 42 |
+------------+------------+------------+
mysql> SELECT
-> COUNT(*) AS total_rows,
-> SUM(legs) AS total_legs,
-> SUM(arms) AS total_arms,
-> AVG(legs) AS avg_legs,
-> AVG(arms) AS avg_arms
-> FROM limbs;
+------------+------------+------------+----------+----------+
| total_rows | total_legs | total_arms | avg_legs | avg_arms |
+------------+------------+------------+----------+----------+
| 23 | 239 | 42 | 11.3810 | 2.0000 |
+------------+------------+------------+----------+----------+
1 row in set (0.00 sec)
mysql> SELECT
-> '腿数量统计' AS category,
-> MAX(legs) AS max_legs,
-> MIN(legs) AS min_legs,
-> ROUND(AVG(legs),2) AS avg_legs,
-> SUM(legs) AS total_legs
-> FROM limbs
-> UNION ALL
-> SELECT
-> '手臂数量统计',
-> MAX(arms),
-> MIN(arms),
-> ROUND(AVG(arms),2),
-> SUM(arms)
-> FROM limbs;
+--------------+----------+----------+----------+------------+
| category | max_legs | min_legs | avg_legs | total_legs |
+--------------+----------+----------+----------+------------+
| 腿数量统计 | 100 | 0 | 11.38 | 239 |
| 手臂数量统计 | 10 | 0 | 2.00 | 42 |
+--------------+----------+----------+----------+------------+
2 rows in set (0.00 sec)
💡 优化建议
1. 添加表注释和列注释
sql
复制
下载
ALTER TABLE limbs
COMMENT '生物肢体数量统计表',
MODIFY COLUMN legs INT COMMENT '腿的数量',
MODIFY COLUMN arms INT COMMENT '手臂的数量';
2. 创建统计视图
sql
复制
下载
CREATE VIEW limbs_stats AS
SELECT
COUNT(*) AS total_species,
SUM(legs) AS total_legs,
SUM(arms) AS total_arms
FROM limbs;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM limbs_stats;
+---------------+------------+------------+
| total_species | total_legs | total_arms |
+---------------+------------+------------+
| 23 | 239 | 42 |
+---------------+------------+------------+
1 row in set (0.00 sec)
mysql> DESCRIBE limbs_stats;
+---------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| total_species | bigint | NO | | 0 | |
| total_legs | decimal(32,0) | YES | | NULL | |
| total_arms | decimal(32,0) | YES | | NULL | |
+---------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> SHOW FULL TABLES WHERE TABLE_TYPE LIKE 'VIEW';
+--------------------+------------+
| Tables_in_cookbook | Table_type |
+--------------------+------------+
| limbs_stats | VIEW |
| mail_view | VIEW |
+--------------------+------------+
2 rows in set (0.00 sec)
📌 视图存储位置
物理存储：视图的定义存储在 information_schema.views 表中
查看定义：
sql
SELECT * FROM information_schema.views
WHERE TABLE_NAME = 'limbs_stats';
💡 视图使用技巧
组合查询：
sql
SELECT
l.thing,
l.legs,
s.total_legs,
ROUND(l.legs/s.total_legs*100,2) AS percent
FROM limbs l, limbs_stats s
WHERE l.legs > 0;
更新视图：
sql
CREATE OR REPLACE VIEW limbs_stats AS
SELECT
COUNT(*) AS total_species,
SUM(legs) AS total_legs,
SUM(arms) AS total_arms,
AVG(legs) AS avg_legs -- 新增字段
FROM limbs;
删除视图：
sql
DROP VIEW IF EXISTS limbs_stats;
- 定时统计任务
sql
复制
下载
– 每天凌晨统计（需事件调度器开启）
CREATE EVENT daily_limbs_stats
ON SCHEDULE EVERY 1 DAY STARTS ‘03:00:00’
DO
INSERT INTO stats_log
SELECT NOW(), * FROM limbs_stats;
⚠️ 预防措施
- 定期检查表健康状态
sql
复制
下载
CHECK TABLE limbs;
ANALYZE TABLE limbs; - 设置自动备份
bash
复制
下载
每天备份（Linux crontab示例）
0 2 * * * mysqldump -u root -p cookbook limbs > /backups/limbs_$(date +%F).sql
3. 监控异常查询
sql
复制
下载
– 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
🌰 实用查询示例
- 按肢体数量分组
sql
复制
下载
SELECT
CONCAT('有', legs, '条腿') AS description,
COUNT(*) AS count
FROM limbs
GROUP BY legs
ORDER BY legs DESC;
- 找出肢体异常的生物
sql
复制
下载
SELECT thing, legs, arms
FROM limbs
WHERE legs > 10 OR arms > 4; - 导出最新统计
sql
复制
下载
SELECT * FROM limbs_stats
INTO OUTFILE ‘/var/lib/mysql-files/limbs_stats.csv’
FIELDS TERMINATED BY ‘,’;
D:\software\MySql\bin>mysql -u root -proot -e "SELECT * FROM limbs " cookbook | summarize
‘summarize’ 不是内部或外部命令，也不是可运行的程序
或批处理文件。
正确的命令示例
D:\software\MySql\bin>mysql -u root -proot -e "SELECT COUNT(*) AS total_rows FROM limbs" cookbook
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| total_rows |
+------------+
| 23 |
+------------+
bash
# 直接在SQL中统计
mysql -u root -proot -e "SELECT COUNT(*) AS total_rows FROM limbs" cookbook
# 导出为CSV
mysql -u root -proot --batch --raw -e "SELECT * FROM limbs" cookbook > limbs.csv
总结
| summarize这个命令是错误的根源。你应该在 SQL 查询中完成数据汇总，或者把数据导出后用其他工具处理。要是你需要更复杂的统计功能，可以考虑学习使用 PowerShell 或者 Python 来处理从 MySQL 导出的数据。 ( P61/951 Saturday, May 17, 2025)
💡 高级技巧
- 导出为Excel格式
```sql
sql
SELECT * FROM limbs
INTO OUTFILE 'D:\\software\\MySql\\Data\\Uploads\\limbs.xls'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n';
Query OK, 23 rows affected (0.01 sec)
2. 自动化导出脚本
cmd
```sql
mysql -u root -p -e "
SELECT * FROM limbs
INTO OUTFILE 'D:/software/MySql/Data/Uploads/limbs_auto.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
" cookbook
- 定时任务（每天备份）
cmd
schtasks /create /tn “MySQL Export” /tr “mysql -u root -p密码 -e “SELECT * FROM limbs INTO OUTFILE ‘D:/backups/limbs_%date:0,4%%date:5,2%%date:~8,2%.csv’ FIELDS TERMINATED BY ‘,’” cookbook” /sc daily /st 02:00
⚠️ 重要注意事项
文件权限
每次导出需确保目标文件 不存在（MySQL不会覆盖）
可先删除旧文件：
sql
! del “D:\software\MySql\Data\Uploads\limbs.csv”
字符编码
如遇乱码，可指定编码：
sql
CHARACTER SET utf8mb4
大文件优化
超过100MB的数据建议分批导出：
```sql
sql
SELECT * FROM limbs WHERE id < 1000 INTO OUTFILE '...part1.csv';
SELECT * FROM limbs WHERE id >= 1000 INTO OUTFILE '...part2.csv';
```sql
mysql> SELECT table_name,
-> ROUND(data_length/1024/1024,2) AS "Size (MB)"
-> FROM information_schema.tables
-> WHERE table_schema = 'cookbook';
+-----------------------+-----------+
| TABLE_NAME | Size (MB) |
+-----------------------+-----------+
| actors | 0.02 |
| adcount | 0.02 |
| al_winner | 0.02 |
| app_log | 0.02 |
| artist | 0.02 |
| book_authors | 0.02 |
| book_vendor | 0.02 |
| booksales | 0.02 |
| catalog_list | 0.02 |
| cd | 0.02 |
| city | 0.02 |
| color | 0.02 |
| cow_color | 0.02 |
| cow_order | 0.02 |
| date_val | 0.02 |
| datetbl | 0.02 |
| datetime_val | 0.02 |
| die | 0.02 |
| doremi | 0.02 |
| drawing | 0.02 |
| driver_log | 0.02 |
| expt | 0.02 |
| formula1 | 0.02 |
| goods_characteristics | 7.52 |
| goods_shops | 4.52 |
| groceries | 0.02 |
| groceries_order_items | 0.02 |
| hitcount | 0.02 |
| hitlog | 0.02 |
| hostip | 0.02 |
| hostname | 0.02 |
| housewares | 0.02 |
| housewares2 | 0.02 |
| housewares3 | 0.02 |
| housewares4 | 0.02 |
| httpdlog | 0.02 |
| httpdlog2 | 0.02 |
| hw_category | 0.02 |
| image | 0.02 |
| ingredient | 0.02 |
| insect | 0.02 |
| inv_item | 0.02 |
| invoice | 0.02 |
| item | 0.02 |
| limbs | 0.02 |
| mail | 0.02 |
| mail_view | NULL |
| marathon | 0.02 |
| mark_log | 0.02 |
| metal | 0.02 |
| money | 0.02 |
| movies | 0.02 |
| movies_actors | 0.02 |
| movies_actors_link | 0.02 |
| mytable | 0.02 |
| name | 0.02 |
| news | 0.02 |
| newsstaff | 0.02 |
| numbers | 0.02 |
| obs | 0.02 |
| occasion | 0.02 |
| painting | 0.02 |
| passtbl | 0.02 |
| passwd | 0.02 |
| patients | 0.02 |
| perl_session | 0.02 |
| person | 0.02 |
| php_session | 0.02 |
| phrase | 0.02 |
| player_stats | 0.02 |
| player_stats2 | 0.02 |
| poi | 0.02 |
| poll_vote | 0.02 |
| profile | 0.02 |
| profile_contact | 0.02 |
| rainfall | 0.02 |
| rand_names | 0.02 |
| rank | 0.02 |
| ranks | 0.02 |
| reviews | 0.02 |
| roster | 0.02 |
| ruby_session | 0.02 |
| sales_region | 0.02 |
| sales_tax_rate | 0.02 |
| sales_volume | 0.02 |
| sibling | 0.02 |
| some table | 0.02 |
| standings1 | 0.02 |
| standings2 | 0.02 |
| states | 0.02 |
| str_val | 0.02 |
| sundays | 0.02 |
| taxpayer | 0.02 |
| testscore | 0.02 |
| testscore_withmisses | 0.02 |
| testscore_withmisses2 | 0.02 |
| time_val | 0.02 |
| tmp | 0.02 |
| tomcat_role | 0.02 |
| tomcat_session | 0.02 |
| tomcat_user | 0.02 |
| top_names | 8.52 |
| trip_leg | 0.02 |
| trip_log | 0.02 |
| tsdemo | 0.02 |
| weatherdata | 0.02 |
| weekday | 0.02 |
+-----------------------+-----------+
107 rows in set (0.46 sec)
🔍 数据分析小贴士
- 空间占用TOP3表：
o top_names → 8.52MB
o goods_characteristics → 7.52MB
o goods_shops → 4.52MB
（适合重点关注优化~） - 特殊发现：
o mail_view 显示 NULL，可能是视图或空表
o 107张表全部正常加载，无损坏表