板凳-------Mysql cookbook学习 （三）
1.22 使长输出行更具可读性
mysql> show full columns from limbs;
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| thing | varchar(20) | utf8mb4_0900_ai_ci | YES | | NULL | | select,insert,update,references | |
| legs | int | NULL | YES | | NULL | | select,insert,update,references | |
| arms | int | NULL | YES | | NULL | | select,insert,update,references | |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
3 rows in set (0.11 sec)mysql> show full columns from limbs\g
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| thing | varchar(20) | utf8mb4_0900_ai_ci | YES | | NULL | | select,insert,update,references | |
| legs | int | NULL | YES | | NULL | | select,insert,update,references | |
| arms | int | NULL | YES | | NULL | | select,insert,update,references | |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
3 rows in set (0.01 sec)
1.23 控制mysql的繁冗级别
D:\software\MySql\bin>echo "select now()" | mysql -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"select now()"' at line 1
这个错误是因为在 Windows 的 echo 命令中，双引号被直接传递给了 MySQL，导致语法错误
D:\software\MySql\bin>echo select now() | mysql -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
now()
2025-05-18 09:54:49D:\software\MySql\bin>mysql -u root -proot -e "select now()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------+
| now() |
+---------------------+
| 2025-05-18 09:56:46 |
+---------------------+-v, --verbose Write more. (-v -v -v gives the table output format).
D:\software\MySql\bin>echo select now() | mysql -u root -proot -v
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select now()
--------------now()
2025-05-18 09:59:14D:\software\MySql\bin>echo select now() | mysql -u root -proot -vv
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select now()
--------------now()
2025-05-18 09:59:27
1 row in setByeD:\software\MySql\bin>echo select now() | mysql -u root -proot -vvv
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select now()
--------------+---------------------+
| now() |
+---------------------+
| 2025-05-18 09:59:36 |
+---------------------+
1 row in set (0.00 sec)Bye
-s, --silent Be more silent. Print results with a tab as separator,each row on new line.
D:\software\MySql\bin>echo select now() | mysql -u root -proot -s
mysql: [Warning] Using a password on the command line interface can be insecure.
2025-05-18 10:05:11D:\software\MySql\bin>echo select now() | mysql -u root -proot -sss
mysql: [Warning] Using a password on the command line interface can be insecure.
2025-05-18 10:05:18
1.24 记录交互式的mysql会话
--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.
————————————————
–tee=D:\sql\Mysql_learning\tmp.out
作用：开启 日志记录功能，将客户端的输入（SQL 语句）和输出（查询结果、错误信息等）实时写入指定文件 tmp.out。
细节：
文件路径：需确保路径存在且当前用户有写入权限。
日志内容：包括连接成功后的所有操作记录，常用于调试、审计或学习复盘（如本例中用于记录 MySQL 学习过程）。
关闭日志：可执行 notee 命令停止记录，或退出客户端自动终止。 \T或 \t 来开启或关闭 tee的输出。
开启日志记录：将后续所有操作及结果保存到 D:\sql\Mysql_learning\tmp.out 文件中。
进入交互模式：等待用户输入 SQL 语句，执行后显示结果并记录到日志文件。
典型使用场景
学习与调试：记录 SQL 练习过程，方便复盘和排查错误。
审计与追溯：在生产环境中记录关键操作，用于合规性检查或问题追溯（需注意权限和安全性）。
批量操作记录：配合脚本执行批量 SQL 时，保存执行过程和结果。
D:\software\MySql\bin>mysql -u root -proot --tee=D:\sql\Mysql_learning\tmp.out cookbook
mysql: [Warning] Using a password on the command line interface can be insecure.
Logging to file 'D:\sql\Mysql_learning\tmp.out'
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 74
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>mysql> \T D:\sql\Mysql_learning\tmp.out
Logging to file 'D:\sql\Mysql_learning\tmp.out'
mysql> \t D:\sql\Mysql_learning\tmp.out
Outfile disabled.
mysql Ver 8.0.40 for Win64 on x86_64 (MySQL Community Server - GPL)Connection id: 74
Current database: cookbook
Current user: root@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Using delimiter: ;
Server version: 8.0.40 MySQL Community Server - GPL
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Binary data as: Hexadecimal
Uptime: 8 days 13 hours 28 min 20 sec
Threads: 3 Questions: 17190 Slow queries: 0 Opens: 1319 Flush tables: 4 Open tables: 37 Queries per second avg: 0.023
ERROR:
Unknown command ‘\M’.
Outfile disabled.
-> \c
1.25 以之前执行的语句创建mysql脚本
通过在mysql中使用–tee命令选项或\T命令可以记录mysql 会话的所有或部分。
开启日志：\T /path/to/new_logfile.log
关闭日志：\t（小写 t）
mysql> \T C:\mysql\part1.log -- 开始记录到 part1.log
mysql> SELECT * FROM users; -- 查询会被记录
mysql> \T C:\mysql\part2.log -- 切换到新日志文件
mysql> INSERT INTO orders ... -- 此操作记录到 part2.log
mysql> \t -- 停止记录
两者的核心区别
特性 --tee 选项 \T 命令
生效时机 客户端启动时一次性设置 会话中随时启用 / 禁用
日志文件切换 无法中途更换文件 可随时切换到新文件
适用场景 完整记录整个会话 分段记录或选择性记录
命令形式 命令行参数（非 SQL 语法） SQL 会话中的命令（以 \ 开头）
\o 命令 -o, --one-database Ignore statements except those that occur while thedefault database is the one named at the command line.
将后续输出仅写入文件，不显示在终端（与 --tee/ \T 的区别在于不显示到屏幕）。
sql
mysql> \o C:\mysql\silent_output.log
mysql> SELECT * FROM large_table; -- 结果只写入文件，不显示
system 命令
在不退出 MySQL 会话的情况下执行系统命令（如查看日志文件大小）。
sql
mysql> system ls -lh /var/log/mysql/
1.26 在sql语句中使用用户自定义的变量管理customers表。
- 创建customers表
首先，你需要一个customers表。以下是一个创建表的示例SQL语句：
mysql> CREATE TABLE IF NOT EXISTS customers (-> cust_id VARCHAR(255) NOT NULL,-> first_name VARCHAR(100) DEFAULT NULL,-> last_name VARCHAR(100) DEFAULT NULL,-> email VARCHAR(255) UNIQUE DEFAULT NULL,-> phone_number VARCHAR(20) DEFAULT NULL,-> address_line_1 VARCHAR(255) DEFAULT NULL,-> address_line_2 VARCHAR(255) DEFAULT NULL,-> city VARCHAR(100) DEFAULT NULL,-> state_province VARCHAR(100) DEFAULT NULL,-> postal_code VARCHAR(20) DEFAULT NULL,-> country VARCHAR(100) DEFAULT NULL,-> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,-> updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,-> PRIMARY KEY (cust_id)-> );
Query OK, 0 rows affected (0.11 sec)mysql> -- 向 Customers 表中插入示例数据
mysql> INSERT INTO `Customers` (`cust_id`, `first_name`, `last_name`, `email`, `phone_number`, `address_line_1`, `city`, `state_province`, `postal_code`, `country`) VALUES-> ('CUST001', 'John', 'Doe', 'john.doe@example.com', '+1-800-123-4567', '123 Main St.', 'Springfield', 'IL', '62704', 'USA'),-> ('CUST002', 'Jane', 'Smith', 'jane.smith@example.com', '+1-800-987-6543', '456 Elm St.', 'Shelbyville', 'KY', '40065', 'USA'),-> ('CUST003', 'Alice', 'Johnson', 'alice.johnson@example.com', '+1-800-555-1212', '789 Oak Ave.', 'Capital City', 'TX', '76101', 'USA');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
代替
CREATE TABLE customers (customer_id INT AUTO_INCREMENT PRIMARY KEY,first_name VARCHAR(50) NOT NULL,last_name VARCHAR(50) NOT NULL,email VARCHAR(100) NOT NULL,phone VARCHAR(20),address VARCHAR(255),city VARCHAR(50),state VARCHAR(50),zip_code VARCHAR(10),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
- 插入数据到customers表
向表中插入数据可以使用以下SQL命令：
INSERT INTO customers (first_name, last_name, email, phone, address, city, state, zip_code)
VALUES ('John', 'Doe', 'john.doe@example.com', '123-456-7890', '123 Main St', 'Anytown', 'CA', '12345');
- 查询customers表中的数据
查询所有客户：
SELECT * FROM customers;
查询特定条件的客户，例如按城市查询：
SELECT * FROM customers WHERE city = ‘Anytown’;
4. 更新customers表中的数据
更新特定客户的邮箱：
UPDATE customers SET email = ‘new.email@example.com’ WHERE customer_id = 1;
5. 删除customers表中的数据
删除特定客户：
DELETE FROM customers WHERE customer_id = 1;
mysql> select @max_limbs := MAX(arms+legs) from limbs;
+------------------------------+
| @max_limbs := MAX(arms+legs) |
+------------------------------+
| 100 |
+------------------------------+
1 row in set, 1 warning (0.01 sec)mysql> SELECT LAST_INSERT_ID() AS new_customer_id;
+-----------------+
| new_customer_id |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)mysql> SELECT MAX(customer_id) FROM customers;
+------------------+
| MAX(customer_id) |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)mysql> SELECT customer_id FROM customers-> WHERE first_name = 'John' AND last_name = 'Doe';
+-------------+
| customer_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)mysql> SELECT @john_id := customer_id FROM customers-> WHERE first_name = 'John' AND last_name = 'Doe';
+-------------------------+
| @john_id := customer_id |
+-------------------------+
| 1 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)
完整的示例：
mysql> -- 插入新客户
mysql> INSERT INTO customers (first_name, last_name, email)-> VALUES ('Jane', 'Smith', 'jane.smith@example.com');
Query OK, 1 row affected (0.01 sec)mysql>
mysql> -- 获取新插入的ID
mysql> SET @new_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> -- 使用变量查询
mysql> SELECT * FROM customers WHERE customer_id = @new_id;
+-------------+------------+-----------+------------------------+-------+---------+------+-------+----------+---------------------+
| customer_id | first_name | last_name | email | phone | address | city | state | zip_code | created_at |
+-------------+------------+-----------+------------------------+-------+---------+------+-------+----------+---------------------+
| 2 | Jane | Smith | jane.smith@example.com | NULL | NULL | NULL | NULL | NULL | 2025-05-18 11:26:49 |
+-------------+------------+-----------+------------------------+-------+---------+------+-------+----------+---------------------+
1 row in set (0.00 sec)
Windows10系统安装perl命令
https://blog.csdn.net/m0_37542524/article/details/130095555
mysql> select @name := thing from limbs where legs = 0;
+----------------+
| @name := thing |
+----------------+
| squid |
| octopus |
| fish |
| phonograph |
| fish |
| phonograph |
| squid |
+----------------+
7 rows in set, 1 warning (0.00 sec)mysql> select @name;
+-------+
| @name |
+-------+
| squid |
+-------+
1 row in set (0.00 sec)mysql> select @name2 := thing from limbs where legs < 0;
Empty set, 1 warning (0.00 sec)mysql> select @name2;
+--------+
| @name2 |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)mysql> set @sum = 4 + 7;
Query OK, 0 rows affected (0.00 sec)mysql> select @sum;
+------+
| @sum |
+------+
| 11 |
+------+
1 row in set (0.00 sec)mysql> set @max_limbs = (select max(arms + legs) from limbs);
Query OK, 0 rows affected (0.00 sec)mysql> select @max_limbs;
+------------+
| @max_limbs |
+------------+
| 100 |
+------------+
1 row in set (0.00 sec)mysql> set @x = 1, @x = 2; select @x, @x;
Query OK, 0 rows affected (0.00 sec)+------+------+
| @x | @x |
+------+------+
| 2 | 2 |
+------+------+
1 row in set (0.00 sec)
1.27 为查询输出行计数
mysql -u root -proot --skip-column-names -e "select thing, arms from limbs cookbook" | cat -n
其中的 cat -n 是 Linux/macOS 系统用于显示文件内容并添加行号的命令，Windows 系统不支持。
使用 Windows 等效命令 more +n
mysql -u root -proot --skip-column-names -e "select thing, arms from limbs" cookbook | more +n
more +n：显示内容并添加行号（+n 表示从第 n 行开始显示，省略则默认从第 1 行开始）。
– 假设limbs是表名，cookbook是数据库名，应在连接时指定数据库：
D:\software\MySql\bin>mysql -u root -proot --skip-column-names -e "select thing, arms from limbs" cookbook | more +1
mysql: [Warning] Using a password on the command line interface can be insecure.
insect 0
squid 10
octopus 8
fish 0
centipede 0
table 0
armchair 2
phonograph 1
tripod 0
Peg Leg Pete 2
space alien NULL
armchair 2
centipede 0
fish 0
human 2
insect 0
Peg Leg Pete 2
phonograph 1
space alien NULL
squid 10
table 0
tripod 0-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.
————————————————
bash
mysql -u root -proot --skip-column-names -D cookbook -e "select thing, arms from limbs"
D:\software\MySql\bin>mysql -u root -proot --skip-column-names -D cookbook -e "select thing, arms from limbs"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+------+
| human | 2 |
| insect | 0 |
| squid | 10 |
| octopus | 8 |
| fish | 0 |
| centipede | 0 |
| table | 0 |
| armchair | 2 |
| phonograph | 1 |
| tripod | 0 |
| Peg Leg Pete | 2 |
| space alien | NULL |
| armchair | 2 |
| centipede | 0 |
| fish | 0 |
| human | 2 |
| insect | 0 |
| Peg Leg Pete | 2 |
| phonograph | 1 |
| space alien | NULL |
| squid | 10 |
| table | 0 |
| tripod | 0 |
+--------------+------+
只需查看结果的行号，可在 SQL 中直接实现：
mysql> SELECT @row:=@row+1 AS row_num, thing, arms-> FROM limbs, (SELECT @row:=0) AS t;
+---------+--------------+------+
| row_num | thing | arms |
+---------+--------------+------+
| 1 | human | 2 |
| 2 | insect | 0 |
| 3 | squid | 10 |
| 4 | octopus | 8 |
| 5 | fish | 0 |
| 6 | centipede | 0 |
| 7 | table | 0 |
| 8 | armchair | 2 |
| 9 | phonograph | 1 |
| 10 | tripod | 0 |
| 11 | Peg Leg Pete | 2 |
| 12 | space alien | NULL |
| 13 | armchair | 2 |
| 14 | centipede | 0 |
| 15 | fish | 0 |
| 16 | human | 2 |
| 17 | insect | 0 |
| 18 | Peg Leg Pete | 2 |
| 19 | phonograph | 1 |
| 20 | space alien | NULL |
| 21 | squid | 10 |
| 22 | table | 0 |
| 23 | tripod | 0 |
+---------+--------------+------+
23 rows in set, 2 warnings (0.01 sec)
使用用户变量，表达式对于查询的每一行都进行计算。
mysql> set @n = 0;
Query OK, 0 rows affected (0.00 sec)mysql> select @n := @n+1 as rownum, thing, arms, legs from limbs;
+--------+--------------+------+------+
| rownum | thing | arms | legs |
+--------+--------------+------+------+
| 1 | human | 2 | 2 |
| 2 | insect | 0 | 6 |
| 3 | squid | 10 | 0 |
| 4 | octopus | 8 | 0 |
| 5 | fish | 0 | 0 |
| 6 | centipede | 0 | 100 |
| 7 | table | 0 | 4 |
| 8 | armchair | 2 | 4 |
| 9 | phonograph | 1 | 0 |
| 10 | tripod | 0 | 3 |
| 11 | Peg Leg Pete | 2 | 1 |
| 12 | space alien | NULL | NULL |
| 13 | armchair | 2 | 4 |
| 14 | centipede | 0 | 99 |
| 15 | fish | 0 | 0 |
| 16 | human | 2 | 2 |
| 17 | insect | 0 | 6 |
| 18 | Peg Leg Pete | 2 | 1 |
| 19 | phonograph | 1 | 0 |
| 20 | space alien | NULL | NULL |
| 21 | squid | 10 | 0 |
| 22 | table | 0 | 4 |
| 23 | tripod | 0 | 3 |
+--------+--------------+------+------+
23 rows in set, 1 warning (0.00 sec)1.28 将mysql用作计算器
mysql> select(17 + 23) / sqrt(64);
+----------------------+
| (17 + 23) / sqrt(64) |
+----------------------+
| 5 |
+----------------------+
1 row in set (0.00 sec)mysql> select 'ABC'= 'abc';
+--------------+
| 'ABC'= 'abc' |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)mysql> set @daily_room_charge = 100.00;
Query OK, 0 rows affected (0.00 sec)mysql> set @num_of_nights = 3;
Query OK, 0 rows affected (0.00 sec)mysql> set @tax_precent = 8;
Query OK, 0 rows affected (0.00 sec)mysql> set @total_room_charge = @daily_room_charge * @num_of_nights;
Query OK, 0 rows affected (0.00 sec)mysql> set @tax = (@total_room_charge * @tax_precent)/100;
Query OK, 0 rows affected (0.00 sec)mysql> set @total = @total_room_charge + @tax;
Query OK, 0 rows affected (0.00 sec)mysql> select @total;
+---------------+
| @total |
+---------------+
| 324.000000000 |
+---------------+
1 row in set (0.00 sec)
1.29 在shell脚本中使用mysql
Windows 系统不支持 Linux 命令（如grep）导致的。
D:\software\MySql\bin>mysql -u root -proot -e status | grep “^uptime”
‘grep’ 不是内部或外部命令，也不是可运行的程序
或批处理文件。
D:\software\MySql\bin>mysql -u root -proot -e "status" | findstr /B "Uptime"
mysql: [Warning] Using a password on the command line interface can be insecure.
Uptime: 8 days 18 hours 37 min 16 secD:\software\MySql\bin>mysql -u root -proot -e "SHOW GLOBAL STATUS LIKE 'Uptime';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 758359 |
+---------------+--------+
1.使用 PowerShell（最简单）
按 Win + X 选择 PowerShell（管理员）。
PS D:\software\MySql\bin> .\mysql -u root -proot -Bse "SHOW GLOBAL STATUS LIKE 'Uptime';" | ForEach-Object { [regex]::Match($_, "\d+").Value }
mysql: [Warning] Using a password on the command line interface can be insecure.
758695
2.使用 CMD 纯命令（无需额外工具）
D:\software\MySql\bin>mysql -u root -proot -e "SHOW GLOBAL STATUS LIKE 'Uptime';" | findstr /C:"Uptime" | findstr /r "[0-9]"
mysql: [Warning] Using a password on the command line interface can be insecure.
Uptime 758881
3.将 MySQL 的 bin 目录添加到系统环境变量（推荐）
这样可以在任意目录直接使用mysql命令，无需每次指定路径：
右键点击 “此电脑” → 属性 → 高级系统设置 → 环境变量。
在系统变量中找到Path，点击编辑。
新建并添加 MySQL 的bin目录路径：D:\software\MySql\bin。
确认保存，重启 PowerShell。
（！未操作成功，以待日后来处理 20250518）
使用完整路径
使用 & 符号作为调用操作符，告诉 PowerShell 将后面的内容作为命令执行。
PS C:\windows\system32> &D:\software\MySql\bin\mysql -u root -proot -Bse "SHOW GLOBAL STATUS LIKE 'Uptime';" | ForEach-Object { [regex]::Match($_, "\d+").Value }
mysql: [Warning] Using a password on the command line interface can be insecure.
761228
PS C:\windows\system32> # 交互式输入密码（不显示在命令历史中）
PS C:\windows\system32> &D:\software\MySql\bin\mysql -u root -p -Bse "SHOW GLOBAL STATUS LIKE 'Uptime';" | ForEach-Object { [regex]::Match($_, "\d+").Value }
Enter password: ****
761426
# 查看 MySQL 版本
PS C:\windows\system32> &D:\software\MySql\bin\mysql -u root -p -Bse "SELECT VERSION();"
Enter password: ****
8.0.40
# 获取服务器负载信息
PS C:\windows\system32> &D:\software\MySql\bin\mysql -u root -p -Bse "SHOW GLOBAL STATUS LIKE 'Threads_running';"
Enter password: ****
Threads_running 2
mysql> select count(*) as 'new log entries:'-> from log_tbl-> where date_added = date_sub(curdate(), interval 1 day);
ERROR 1146 (42S02): Table 'cookbook.log_tbl' doesn't exist
这个错误表明您正在尝试查询一个不存在的表 log_tbl。让我们一步步解决这个问题：
解决方案
- 首先确认数据库中存在的表
sql
SHOW TABLES; - 如果您确实需要 log_tbl 表，请先创建它
sql
CREATE TABLE IF NOT EXISTS log_tbl (id INT AUTO_INCREMENT PRIMARY KEY,log_message TEXT,date_added DATE,-- 其他您需要的字段INDEX (date_added) -- 为日期字段添加索引提高查询性能
);
- 如果您想查询其他日志表
如果您实际想查询的是其他日志表（比如 app_log），可以修改查询：
sql
SELECT COUNT(*) AS 'new log entries:'
FROM app_log -- 替换为实际的日志表名
WHERE date_added = DATE_SUB(CURDATE(), INTERVAL 1 DAY);
- 如果您想创建每日日志统计
sql
-- 创建日志统计表
CREATE TABLE IF NOT EXISTS daily_log_stats (stat_date DATE PRIMARY KEY,entry_count INT,last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
– 插入昨日日志统计
INSERT INTO daily_log_stats (stat_date, entry_count)
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS stat_date,COUNT(*) AS entry_count
FROM app_log -- 使用您实际的日志表
WHERE date_added = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
ON DUPLICATE KEY UPDATE entry_count = VALUES(entry_count);
关键点说明
错误原因：ERROR 1146 表示表不存在
日期函数：
CURDATE() 获取当前日期
DATE_SUB() 用于日期计算
索引建议：日志表应该为日期字段添加索引
预防措施
在查询前总是检查表是否存在：
sql
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'log_tbl';
使用事务确保数据一致性：sql
START TRANSACTION;
-- 您的查询语句
COMMIT;
D:\software\MySql\bin>mysql -u root -p p72/951 Sunday, May 18, 2025