板凳-------Mysql cookbook学习 （二）

生成一个包含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\binD:\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

---

🎁 新功能说明

1. 日志记录 - 所有操作记录到 backup_log.txt
2. 自动轮替 - 只保留最新5份备份（可修改MAX_BACKUPS）
3. 错误处理 - 明确显示备份成功/失败
4. 智能清理 - 按修改时间自动删除旧备份

---

🛠️ 使用方法

1. 将上方代码保存为 mysql_backup.bat
2. 右键 → 以管理员身份运行
3. 输入密码后，会自动：
   o 生成带时间戳的备份文件
   o 记录操作日志
   o 清理旧备份

---

🌟 高级技巧
如果想每天自动备份，可以：

1. 打开 任务计划程序
2. 创建基本任务 → 选择该脚本
3. 设置为每天凌晨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 到系统环境变量）

1. 打开环境变量设置
   o Win + R → 输入 sysdm.cpl → 高级 → 环境变量
2. 修改 PATH
   o 在 系统变量 中找到 Path → 编辑
   o 点击 新建，添加你的 MySQL bin 目录（如 D:\software\MySql\bin）
3. 保存并重启 CMD
   o 关闭所有 CMD/PowerShell 窗口，重新打开再试。

---

方法 3：检查脚本编码（避免中文乱码）

1. 用 记事本 打开 .bat 文件
2. 另存为 → 选择 编码: ANSI（不要用 UTF-8）
3. 重新运行脚本

---

🔍 错误排查
如果仍然失败，可以手动测试：
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点自动备份

1. 按 Win+R 输入 taskschd.msc
2. 创建任务 → 触发器选择「每天 3:00」
3. 操作选择「启动程序」指向您的 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.logtype "%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. 错误1：Failed to open file ‘test.sql’, error: 2
   o 表示 MySQL 在当前目录找不到 test.sql
2. 错误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 中运行：
cmddir "D:\sql\Mysql_learning\test.sql"

文件编码检查
如果文件内容含特殊字符，建议：

1. 用 Notepad++ 打开文件
2. 编码 → 转为 UTF-8 without BOM
3. 保存后重试
   权限问题排查

cmdicacls "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 cookbookD:\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 - GPLCopyright (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 - GPLCopyright (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       0D:\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.txtmysql> 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 startupand reconnecting may take a longer time. Disable with--disable-auto-rehash.(Defaults to on; use --skip-auto-rehash to disable.)-A, --no-auto-rehashNo automatic rehashing. One has to use 'rehash' to gettable and field completion. This gives a quicker start ofmysql and disables rehashing on reconnect.--auto-vertical-outputAutomatically switch to vertical output mode if theresult 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 forinteractive terminals.--character-sets-dir=nameDirectory for character set files.--column-type-info  Display column type information.-c, --comments      Preserve comments. Send comments to the server. Thedefault is --skip-comments (discard comments), enablewith --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=nameSet the default character set.--delimiter=name    Delimiter to be used.--enable-cleartext-pluginEnable/disable the clear text authentication plugin.-e, --execute=name  Execute command and quit. (Disables --force and historyfile.)-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 statementsfrom getting logged into syslog and mysql history.-G, --named-commandsEnable named commands. Named commands mean this program'sinternal commands; see mysql> help . When enabled, thenamed 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 isdisabled 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-numbersDon'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-namesDon't write column names in results.--sigint-ignore     Ignore SIGINT (CTRL-C).-o, --one-database  Ignore statements except those that occur while thedefault database is the one named at the command line.-p, --password[=name]Password to use when connecting to server. If password isnot 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, inorder 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 slowdown the server if the output is suspended. Doesn't usehistory 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=nameBase name of shared memory.-S, --socket=name   The socket file to use for connection.--server-public-key-path=nameFile path to the server public RSA key in PEM format.--get-server-public-keyGet 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=nameSSL FIPS mode (applies only for OpenSSL); permittedvalues are: OFF, ON, STRICT--tls-ciphersuites=nameTLS v1.3 cipher to use.--ssl-session-data=nameSession data file to use to enable ssl session reuse--ssl-session-data-continue-on-failed-reuseIf set to ON, this option will allow connection tosucceed 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 fromserver.--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 ofcommands depends on the patterns supplied via histignoreoption 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' istranslated 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 inputpiped to mysql or loaded using the 'source' command).This is necessary when processing output from mysqlbinlogthat may contain blobs.--connect-expired-passwordNotify the server that this client is prepared to handleexpired password sandbox mode.--compression-algorithms=nameUse 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 isbetween 1 and 22, inclusive. Default is 3.--load-data-local-dir=nameDirectory path safe for LOAD DATA LOCAL INFILE to readfrom.--fido-register-factor=nameSpecifies authentication factor, for which registrationneeds to be done.--authentication-oci-client-config-profile=nameSpecifies the configuration profile whose configurationoptions are to be read from the OCI configuration file.Default is DEFAULT.--oci-config-file=nameSpecifies the location of the OCI configuration file.Default for Linux is ~/.oci/config and %HOME/.oci/configon Windows.--plugin-authentication-kerberos-client-mode=nameKerberos 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;

3. 定时统计任务
   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;

---

⚠️ 预防措施

1. 定期检查表健康状态
   sql
   复制
   下载
   CHECK TABLE limbs;
   ANALYZE TABLE limbs;
2. 设置自动备份
   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;

---

🌰 实用查询示例

1. 按肢体数量分组
   sql
   复制
   下载

SELECT CONCAT('有', legs, '条腿') AS description,COUNT(*) AS count
FROM limbs
GROUP BY legs
ORDER BY legs DESC;

2. 找出肢体异常的生物
   sql
   复制
   下载
   SELECT thing, legs, arms
   FROM limbs
   WHERE legs > 10 OR arms > 4;
3. 导出最新统计
   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)

💡 高级技巧

1. 导出为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

3. 定时任务（每天备份）
   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)

🔍 数据分析小贴士

1. 空间占用TOP3表：
   o top_names → 8.52MB
   o goods_characteristics → 7.52MB
   o goods_shops → 4.52MB
   （适合重点关注优化~）
2. 特殊发现：
   o mail_view 显示 NULL，可能是视图或空表
   o 107张表全部正常加载，无损坏表