[root@yfw ~]# cd /www/wwwroot/szrengjing.com
[root@yfw szrengjing.com]# mysql -u szrengjing_com -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 93707
Server version: 5.7.42-log Source distribution
Copyright (c) 2000, 2023, 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> USE szrengjing_com;
Database changed
mysql> CREATE TABLE IF NOT EXISTS ecs_user_credit (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> user_id INT NOT NULL UNIQUE,
-> score INT DEFAULT 80,
-> level VARCHAR(20) AS (
-> CASE
-> WHEN score >= 95 THEN '★★★★★'
-> WHEN score >= 85 THEN '★★★★☆'
-> WHEN score >= 75 THEN '★★★☆☆'
-> WHEN score >= 60 THEN '★★☆☆☆'
-> ELSE '★☆☆☆☆'
-> END
-> ) STORED,
-> updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS ecs_user_credit_log (
-> id BIGINT AUTO_INCREMENT PRIMARY KEY,
-> user_id INT NOT NULL,
-> old_score INT,
-> new_score INT,
-> change_value INT,
-> reason VARCHAR(100),
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DROP TRIGGER IF EXISTS tr_after_update_credit;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> DROP PROCEDURE IF EXISTS sp_add_credit;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DELIMITER ;;
mysql> CREATE TRIGGER tr_after_update_credit
-> AFTER UPDATE ON ecs_user_credit
-> FOR EACH ROW
-> BEGIN
-> INSERT INTO ecs_user_credit_log (user_id, old_score, new_score, change_value, reason)
-> VALUES (NEW.user_id, OLD.score, NEW.score, NEW.score - OLD.score, '系统调整');
-> END;;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE PROCEDURE sp_add_credit(
-> IN p_user_id INT,
-> IN p_change INT,
-> IN p_reason VARCHAR(100)
-> )
-> BEGIN
-> DECLARE current_score INT DEFAULT 80;
-> -- 查询当前分数并加锁防止并发
-> SELECT score INTO current_score FROM ecs_user_credit WHERE user_id = p_user_id FOR UPDATE;
->
-> -- 插入或更新分数
-> INSERT INTO ecs_user_credit (user_id, score)
-> VALUES (p_user_id, current_score + p_change)
-> ON DUPLICATE KEY UPDATE score = score + p_change;
-> END;;
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> SHOW TABLES LIKE 'ecs_user_credit%';
+---------------------------------------------+
| Tables_in_szrengjing_com (ecs_user_credit%) |
+---------------------------------------------+
| ecs_user_credit |
| ecs_user_credit_log |
+---------------------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW TRIGGERS LIKE 'ecs_user_credit'\G
*************************** 1. row ***************************
Trigger: tr_update_credit_level
Event: UPDATE
Table: ecs_user_credit
Statement: BEGIN
IF NEW.score >= 95 THEN
SET NEW.level = '★★★★★';
ELSEIF NEW.score >= 85 THEN
SET NEW.level = '★★★★☆';
ELSEIF NEW.score >= 75 THEN
SET NEW.level = '★★★☆☆';
ELSEIF NEW.score >= 60 THEN
SET NEW.level = '★★☆☆☆';
ELSE
SET NEW.level = '★☆☆☆☆';
END IF;
END
Timing: BEFORE
Created: 2025-11-09 05:25:20.26
sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: szrengjing_com@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8mb4_general_ci
*************************** 2. row ***************************
Trigger: tr_after_update_credit
Event: UPDATE
Table: ecs_user_credit
Statement: BEGIN
INSERT INTO ecs_user_credit_log (user_id, old_score, new_score, change_value, reason)
VALUES (NEW.user_id, OLD.score, NEW.score, NEW.score - OLD.score, '系统调整');
END
Timing: AFTER
Created: 2025-11-09 06:19:33.39
sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: szrengjing_com@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8mb4_general_ci
2 rows in set (0.00 sec)
mysql> SHOW PROCEDURE STATUS WHERE Name = 'sp_add_credit'\G
*************************** 1. row ***************************
Db: szrengjing_com
Name: sp_add_credit
Type: PROCEDURE
Definer: szrengjing_com@localhost
Modified: 2025-11-09 06:19:48
Created: 2025-11-09 06:19:48
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)
mysql> CALL sp_add_credit(1, 5, '手动测试加分');
Query OK, 2 rows affected (0.01 sec)
mysql> SELECT * FROM ecs_user_credit_log ORDER BY id DESC LIMIT 1\G
*************************** 1. row ***************************
id: 4
user_id: 1
old_score: 90
new_score: 95
change_value: 5
reason: 系统调整
created_at: 2025-11-09 06:21:06
1 row in set (0.00 sec)
mysql> DROP TRIGGER IF EXISTS tr_update_credit_level;
Query OK, 0 rows affected (0.01 sec)
mysql> CALL sp_add_credit(2, 10, '新用户注册奖励');
Query OK, 1 row affected (0.00 sec)
mysql> CALL sp_add_credit(1, -5, '违规扣分测试');
Query OK, 2 rows affected (0.01 sec)
mysql> SELECT * FROM ecs_user_credit_log ORDER BY id DESC LIMIT 5\G
*************************** 1. row ***************************
id: 5
user_id: 1
old_score: 95
new_score: 90
change_value: -5
reason: 系统调整
created_at: 2025-11-09 06:22:39
*************************** 2. row ***************************
id: 4
user_id: 1
old_score: 90
new_score: 95
change_value: 5
reason: 系统调整
created_at: 2025-11-09 06:21:06
*************************** 3. row ***************************
id: 3
user_id: 1
old_score: 85
new_score: 90
change_value: 5
reason: 测试加分
created_at: 2025-11-09 05:55:47
*************************** 4. row ***************************
id: 2
user_id: 1
old_score: 95
new_score: 85
change_value: -10
reason: 评论被举报
created_at: 2025-11-09 05:38:14
*************************** 5. row ***************************
id: 1
user_id: 1
old_score: 80
new_score: 95
change_value: 15
reason: 完善个人资料
created_at: 2025-11-09 05:38:14
5 rows in set (0.00 sec)
mysql> SELECT user_id, score, level, updated_at FROM ecs_user_credit;
+---------+-------+-----------------+---------------------+
| user_id | score | level | updated_at |
+---------+-------+-----------------+---------------------+
| 1 | 90 | ★★★★★ | 2025-11-09 06:22:39 |
| 2 | 90 | ★★★★☆ | 2025-11-09 06:22:39 |
+---------+-------+-----------------+---------------------+
2 rows in set (0.00 sec)
mysql> CALL sp_add_credit(3, 10, '邀请好友');
Query OK, 1 row affected (0.00 sec)
mysql> DELIMITER ;;
mysql> DROP PROCEDURE IF EXISTS sp_add_credit;;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE sp_add_credit(
-> IN p_user_id INT,
-> IN p_change INT,
-> IN p_reason VARCHAR(100)
-> )
-> BEGIN
-> DECLARE current_score INT DEFAULT 80;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET current_score = 80;
->
-> -- 尝试获取当前分数（如果存在）
-> SELECT score INTO current_score
-> FROM ecs_user_credit
-> WHERE user_id = p_user_id
-> FOR UPDATE;
->
-> -- 插入或更新：不存在则从 80+p_change 开始
-> INSERT INTO ecs_user_credit (user_id, score)
-> VALUES (p_user_id, current_score + p_change)
-> ON DUPLICATE KEY UPDATE score = score + p_change;
-> END;;
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> DELIMITER ;;
mysql> DROP PROCEDURE IF EXISTS sp_add_credit;;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE sp_add_credit(
-> IN p_user_id INT,
-> IN p_change INT,
-> IN p_reason VARCHAR(100)
-> )
-> BEGIN
-> DECLARE current_score INT DEFAULT 80;
-> DECLARE new_score INT;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET current_score = 80;
->
-> -- 获取当前分数（加锁）
-> SELECT score INTO current_score
-> FROM ecs_user_credit
-> WHERE user_id = p_user_id
-> FOR UPDATE;
->
-> -- 计算新分数
-> SET new_score = current_score + p_change;
->
-> -- 限制范围
-> IF new_score < 0 THEN
-> SET new_score = 0;
-> ELSEIF new_score > 100 THEN
-> SET new_score = 100;
-> END IF;
->
-> -- 写入数据库
-> INSERT INTO ecs_user_credit (user_id, score)
-> VALUES (p_user_id, new_score)
-> ON DUPLICATE KEY UPDATE score = new_score;
-> END;;
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> CREATE OR REPLACE VIEW vw_user_credit_summary AS
-> SELECT
-> u.user_id,
-> u.score,
-> u.level,
-> u.updated_at,
-> (
-> SELECT GROUP_CONCAT(reason SEPARATOR '; ')
-> FROM ecs_user_credit_log l
-> WHERE l.user_id = u.user_id
-> ORDER BY l.created_at DESC
-> LIMIT 3
-> ) AS recent_actions
-> FROM ecs_user_credit u
-> ORDER BY u.score DESC;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM vw_user_credit_summary\G
*************************** 1. row ***************************
user_id: 1
score: 90
level: ★★★★★
updated_at: 2025-11-09 06:22:39
recent_actions: 完善个人资料; 评论被举报; 测试加分; 系统调整; 系统调整
*************************** 2. row ***************************
user_id: 2
score: 90
level: ★★★★☆
updated_at: 2025-11-09 06:22:39
recent_actions: NULL
*************************** 3. row ***************************
user_id: 3
score: 90
level: ★★★★☆
updated_at: 2025-11-09 06:24:47
recent_actions: NULL
3 rows in set (0.00 sec)
mysql> -- 日志表按用户和时间查询频繁
mysql> ALTER TABLE ecs_user_credit_log ADD INDEX idx_user_time (user_id, created_at);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 主表按分数排序常用
mysql> ALTER TABLE ecs_user_credit ADD INDEX idx_score (score);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> CREATE PROCEDURE sp_add_credit(...)
-> BEGIN
-> DECLARE current_score INT DEFAULT 80;
ERROR 1064 (42000): 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 '...)
BEGIN
DECLARE current_score INT DEFAULT 80' at line 1
mysql> DECLARE new_score INT;
ERROR 1064 (42000): 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 'DECLARE new_score INT' at line 1
mysql> DECLARE CONTINUE HANDLER FOR NOT FOUND SET current_score = 80;
ERROR 1064 (42000): 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 'DECLARE CONTINUE HANDLER FOR NOT FOUND SET current_score = 80' at line 1
mysql>
mysql> SELECT score INTO current_score FROM ecs_user_credit WHERE user_id = p_user_id FOR UPDATE;
ERROR 1327 (42000): Undeclared variable: current_score
mysql>
mysql> SET new_score = current_score + p_change;
ERROR 1193 (HY000): Unknown system variable 'new_score'
mysql> IF new_score < 0 THEN SET new_score = 0; END IF;
ERROR 1064 (42000): 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 'IF new_score < 0 THEN SET new_score = 0' at line 1
ERROR 1064 (42000): 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 'END IF' at line 1
mysql> IF new_score > 100 THEN SET new_score = 100; END IF;
ERROR 1064 (42000): 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 'IF new_score > 100 THEN SET new_score = 100' at line 1
ERROR 1064 (42000): 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 'END IF' at line 1
mysql>
mysql> INSERT INTO ... ON DUPLICATE KEY UPDATE score = new_score;
ERROR 1064 (42000): 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 '.. ON DUPLICATE KEY UPDATE score = new_score' at line 1
mysql> END;;
ERROR 1064 (42000): 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 'END' at line 1
ERROR:
No query specified
mysql> SELECT * FROM vw_user_credit_summary\G
*************************** 1. row ***************************
user_id: 1
score: 90
level: ★★★★★
updated_at: 2025-11-09 06:22:39
recent_actions: 完善个人资料; 评论被举报; 测试加分; 系统调整; 系统调整
*************************** 2. row ***************************
user_id: 2
score: 90
level: ★★★★☆
updated_at: 2025-11-09 06:22:39
recent_actions: NULL
*************************** 3. row ***************************
user_id: 3
score: 90
level: ★★★★☆
updated_at: 2025-11-09 06:24:47
recent_actions: NULL
3 rows in set (0.00 sec)
mysql> CREATE OR REPLACE VIEW vw_user_credit_summary AS
-> SELECT
-> u.user_id,
-> u.score,
-> u.level,
-> u.updated_at,
-> (
-> SELECT GROUP_CONCAT(reason SEPARATOR '; ')
-> FROM (
-> SELECT reason
-> FROM ecs_user_credit_log l
-> WHERE l.user_id = u.user_id
-> ORDER BY l.created_at DESC
-> LIMIT 3
-> ) AS tmp
-> ) AS recent_actions
-> FROM ecs_user_credit u
-> ORDER BY u.score DESC;
ERROR 1054 (42S22): Unknown column 'u.user_id' in 'where clause'
mysql> SET SESSION group_concat_max_len = 1024;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE ecs_user_credit_log ADD INDEX idx_user_time (user_id, created_at);
ERROR 1061 (42000): Duplicate key name 'idx_user_time'
mysql> ALTER TABLE ecs_user_credit ADD INDEX idx_score (score);
ERROR 1061 (42000): Duplicate key name 'idx_score'
mysql> -- 1. 备份数据
mysql> CREATE TABLE ecs_user_credit_backup AS SELECT * FROM ecs_user_credit;
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 2. 删除旧表
mysql> DROP TABLE ecs_user_credit;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> -- 3. 重新创建带正确生成列的表
mysql> CREATE TABLE ecs_user_credit (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> user_id INT NOT NULL UNIQUE,
-> score INT DEFAULT 80,
-> level VARCHAR(20) AS (
-> CASE
-> WHEN score >= 95 THEN '★★★★★'
-> WHEN score >= 85 THEN '★★★★☆'
-> WHEN score >= 75 THEN '★★★☆☆'
-> WHEN score >= 60 THEN '★★☆☆☆'
-> ELSE '★☆☆☆☆'
-> END
-> ) STORED,
-> updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO ecs_user_credit (id, user_id, score)
-> SELECT id, user_id, score FROM ecs_user_credit_backup;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> DROP TABLE ecs_user_credit_backup;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT user_id, score, level FROM ecs_user_credit;
+---------+-------+-----------------+
| user_id | score | level |
+---------+-------+-----------------+
| 1 | 90 | ★★★★☆ |
| 2 | 90 | ★★★★☆ |
| 3 | 90 | ★★★★☆ |
+---------+-------+-----------------+
3 rows in set (0.00 sec)
mysql> CREATE OR REPLACE VIEW vw_user_credit_summary AS
-> SELECT
-> u.user_id,
-> u.score,
-> u.level,
-> u.updated_at,
-> (
-> SELECT GROUP_CONCAT(tmp.reason SEPARATOR '; ')
-> FROM (
-> SELECT reason
-> FROM ecs_user_credit_log l
-> WHERE l.user_id = u.user_id
-> ORDER BY l.created_at DESC
-> LIMIT 3
-> ) AS tmp
-> ) AS recent_actions
-> FROM ecs_user_credit u
-> ORDER BY u.score DESC;
ERROR 1054 (42S22): Unknown column 'u.user_id' in 'where clause'
mysql> ERROR 1061 (42000): Duplicate key name 'idx_user_time'
-> ^C
mysql> CREATE TABLE ecs_credit_rule (
-> action_code VARCHAR(50) PRIMARY KEY,
-> description VARCHAR(100) NOT NULL,
-> change_value INT NOT NULL,
-> max_times_per_day INT DEFAULT 0 COMMENT '每日最多触发次数'
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> INSERT INTO ecs_credit_rule VALUES
-> ('register', '注册奖励', 10, 1),
-> ('checkin', '签到', 2, 1),
-> ('post', '发帖', 5, 3),
-> ('report', '被举报', -10, 0);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> DELIMITER ;;
mysql> CREATE PROCEDURE sp_add_credit_by_action(
-> IN p_user_id INT,
-> IN p_action_code VARCHAR(50)
-> )
-> BEGIN
-> DECLARE v_change INT;
-> DECLARE v_reason VARCHAR(100);
->
-> -- 查询规则
-> SELECT change_value, description
-> INTO v_change, v_reason
-> FROM ecs_credit_rule
-> WHERE action_code = p_action_code;
->
-> -- 调用主过程
-> CALL sp_add_credit(p_user_id, v_change, v_reason);
-> END;;
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL sp_add_credit_by_action(1, 'checkin');
Query OK, 2 rows affected (0.03 sec)
mysql> -- 备份
mysql> CREATE TABLE ecs_user_credit_backup AS SELECT * FROM ecs_user_credit;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 删除旧表（清除被旧触发器污染的数据）
mysql> DROP TABLE ecs_user_credit;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> -- 重建表：确保 level 是 STORED 生成列
mysql> CREATE TABLE ecs_user_credit (...);
ERROR 1064 (42000): 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 '..)' at line 1
mysql>
mysql> -- 恢复数据
mysql> INSERT INTO ecs_user_credit (id, user_id, score) SELECT id, user_id, score FROM ecs_user_credit_backup;
ERROR 1146 (42S02): Table 'szrengjing_com.ecs_user_credit' doesn't exist
mysql>
mysql> -- 验证结果
mysql> SELECT user_id, score, level FROM ecs_user_credit;
ERROR 1146 (42S02): Table 'szrengjing_com.ecs_user_credit' doesn't exist
mysql> CREATE OR REPLACE VIEW vw_user_credit_summary AS
-> SELECT
-> u.user_id,
-> ...
-> (
-> SELECT GROUP_CONCAT(tmp.reason SEPARATOR '; ')
-> FROM (
-> SELECT reason
-> FROM ecs_user_credit_log l
-> WHERE l.user_id = u.user_id
-> ORDER BY l.created_at DESC
-> LIMIT 3
-> ) AS tmp
-> ) AS recent_actions
-> FROM ecs_user_credit u;
ERROR 1064 (42000): 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 GROUP_CONCAT(tmp.reason SEPARATOR '; ')
FROM (
' at line 4
mysql> CREATE OR REPLACE VIEW vw_user_credit_summary AS
-> SELECT
-> u.user_id,
-> u.score,
-> u.level,
-> u.updated_at,
-> (
-> SELECT GROUP_CONCAT(log_entry.reason SEPARATOR '; ')
-> FROM (
-> SELECT reason
-> FROM ecs_user_credit_log l
-> WHERE l.user_id = u.user_id
-> ORDER BY l.created_at DESC
-> LIMIT 3
-> ) AS log_entry
-> ) AS recent_actions
-> FROM ecs_user_credit u
-> ORDER BY u.score DESC;
ERROR 1146 (42S02): Table 'szrengjing_com.ecs_user_credit' doesn't exist
mysql> CREATE OR REPLACE VIEW vw_user_credit_summary AS
-> SELECT
-> u.user_id,
-> u.score,
-> u.level,
-> u.updated_at,
-> (
-> SELECT GROUP_CONCAT(reason SEPARATOR '; ')
-> FROM (
-> SELECT reason
-> FROM ecs_user_credit_log
-> WHERE user_id = u.user_id
-> ORDER BY created_at DESC
-> LIMIT 3
-> ) AS t
-> ) AS recent_actions
-> FROM ecs_user_credit u
-> ORDER BY u.score DESC;
ERROR 1146 (42S02): Table 'szrengjing_com.ecs_user_credit' doesn't exist
mysql>
records_per_key介绍、场景与计算
扫一扫
305
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
