[root@yfw ~]# cd /www/wwwroot/szrengjing.com
[root@yfw szrengjing.com]# mysql -u szrengjing_com -p szrengjing_com
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 681367
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> -- 1. 查客服分配规则表（ecs_chat_allot_rule）- 全站客服分配规则大概率在这里
mysql> SELECT * FROM ecs_chat_allot_rule WHERE kefu_account = 'kf_rjsc' OR rule_value LIKE '%kf_rjsc%';
ERROR 1054 (42S22): Unknown column 'kefu_account' in 'where clause'
mysql>
mysql> -- 2. 查客服基础信息表（ecs_chat_customer）
mysql> SELECT * FROM ecs_chat_customer WHERE customer_account = 'kf_rjsc' OR customer_name LIKE '%kf_rjsc%';
ERROR 1054 (42S22): Unknown column 'customer_account' in 'where clause'
mysql>
mysql> -- 3. 查客服状态表（ecs_chat_customer_status）
mysql> SELECT * FROM ecs_chat_customer_status WHERE customer_account = 'kf_rjsc';
ERROR 1054 (42S22): Unknown column 'customer_account' in 'where clause'
mysql>
mysql> -- 4. 查聊天会话表（ecs_chat_session）- 看该客服是否被全站会话关联
mysql> SELECT COUNT(*) AS total_session, shop_id FROM ecs_chat_session WHERE kefu_account = 'kf_rjsc' GROUP BY shop_id;
ERROR 1054 (42S22): Unknown column 'shop_id' in 'field list'
mysql> -- 1. 查主站店铺配置中是否把kf_rjsc设为默认客服
mysql> SELECT * FROM ecs_shop_config
-> WHERE (config_key LIKE '%kefu%' OR config_key LIKE '%customer%' OR config_key LIKE '%service%')
-> AND config_value = 'kf_rjsc';
ERROR 1054 (42S22): Unknown column 'config_key' in 'where clause'
mysql>
mysql> -- 2. 查供应商（店铺）专属配置中是否批量配置了kf_rjsc
mysql> SELECT * FROM ecs_supplier_shop_config
-> WHERE (config_key LIKE '%kefu%' OR config_key LIKE '%customer%')
-> AND config_value = 'kf_rjsc';
ERROR 1054 (42S22): Unknown column 'config_key' in 'where clause'
mysql>
mysql> -- 3. 查店铺等级/供应商表 - 看是否把kf_rjsc设为全店铺默认
mysql> SELECT * FROM ecs_shop_grade WHERE grade_config LIKE '%kf_rjsc%';
ERROR 1054 (42S22): Unknown column 'grade_config' in 'where clause'
mysql> SELECT * FROM ecs_supplier WHERE supplier_config LIKE '%kf_rjsc%';
ERROR 1054 (42S22): Unknown column 'supplier_config' in 'where clause'
mysql> -- 1. 查管理员配置表
mysql> SELECT * FROM ecs_admin_user WHERE user_name = 'kf_rjsc' OR password LIKE '%kf_rjsc%' OR remarks LIKE '%kf_rjsc%';
ERROR 1054 (42S22): Unknown column 'remarks' in 'where clause'
mysql>
mysql> -- 2. 查系统配置表（兜底）
mysql> SELECT * FROM ecs_ecsmart_shop_config
-> WHERE (config_key LIKE '%default_kefu%' OR config_key LIKE '%global_service%')
-> AND config_value = 'kf_rjsc';
ERROR 1054 (42S22): Unknown column 'config_key' in 'where clause'
mysql> -- ========== 1. 客服聊天系列表 字段结构 ==========
mysql> -- 客服分配规则表
mysql> DESC ecs_chat_allot_rule;
+-------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+----------------+
| rule_id | int(11) | NO | PRI | NULL | auto_increment |
| supp_id | int(11) | NO | MUL | NULL | |
| cus_id | int(11) | NO | | NULL | |
| priority | int(11) | NO | | 1 | |
| max_session | int(11) | NO | | 50 | |
| is_enable | tinyint(4) | NO | | 1 | |
+-------------+------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> -- 客服基础信息表
mysql> DESC ecs_chat_customer;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| cus_id | mediumint(7) | NO | PRI | NULL | auto_increment |
| user_id | mediumint(7) | NO | | NULL | |
| of_username | varchar(120) | NO | | NULL | |
| cus_name | varchar(120) | NO | | NULL | |
| supp_id | mediumint(7) | NO | | -1 | |
| cus_type | int(10) | NO | | 0 | |
| session_count | int(10) | YES | | 0 | |
| talk_time | int(10) | YES | | 0 | |
| cus_degree | int(10) | YES | | 0 | |
| online_status | tinyint(1) | YES | | 0 | |
| cus_enable | int(10) | YES | | 1 | |
| add_time | int(10) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)
mysql> -- 客服状态表
mysql> DESC ecs_chat_customer_status;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| cus_id | int(11) | NO | PRI | NULL | auto_increment |
| cus_name | varchar(64) | NO | | NULL | |
| supp_id | int(11) | NO | MUL | 0 | |
| is_super | tinyint(1) | YES | MUL | 0 | |
| kefu_type | tinyint(1) | NO | | 1 | |
| is_admin | tinyint(1) | NO | | 0 | |
| status | tinyint(1) | YES | MUL | 1 | |
| busy | tinyint(1) | YES | | 0 | |
| last_active | int(11) | NO | | 0 | |
| create_time | int(11) | NO | | 0 | |
| cus_account | varchar(32) | NO | UNI | NULL | |
| cus_pwd | varchar(32) | NO | | NULL | |
| data_scope | tinyint(4) | NO | | 1 | |
+-------------+-------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)
mysql> -- 聊天会话表
mysql> DESC ecs_chat_session;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| session_id | varchar(64) | NO | PRI | NULL | |
| user_id | int(11) | NO | MUL | NULL | |
| user_mobile | varchar(20) | NO | | | |
| cus_id | int(11) | NO | MUL | NULL | |
| supp_id | int(11) | NO | MUL | NULL | |
| start_time | int(10) | YES | | 0 | |
| last_msg_time | int(11) | NO | | NULL | |
| end_time | int(10) | YES | | 0 | |
| status | tinyint(1) | YES | | 1 | |
| evaluate_score | tinyint(1) | YES | | 0 | |
| evaluate_content | text | YES | | NULL | |
| evaluate_time | int(11) | YES | | 0 | |
| tenant_id | int(11) | NO | | NULL | |
+------------------+-------------+------+-----+---------+-------+
13 rows in set (0.00 sec)
mysql>
mysql> -- ========== 2. 店铺配置系列表 字段结构 ==========
mysql> -- 主站店铺配置表
mysql> DESC ecs_shop_config;
+-------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| parent_id | smallint(5) unsigned | NO | MUL | 0 | |
| code | varchar(30) | NO | UNI | | |
| type | varchar(10) | NO | | | |
| store_range | varchar(255) | NO | | | |
| store_dir | varchar(255) | NO | | | |
| value | text | NO | | NULL | |
| sort_order | tinyint(3) unsigned | NO | | 1 | |
+-------------+----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> -- 供应商店铺配置表
mysql> DESC ecs_supplier_shop_config;
+-------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | 0 | |
| parent_id | smallint(5) unsigned | NO | MUL | 0 | |
| code | varchar(30) | NO | | | |
| type | varchar(10) | NO | | | |
| store_range | varchar(255) | NO | | | |
| store_dir | varchar(255) | NO | | | |
| value | text | NO | | NULL | |
| sort_order | tinyint(3) unsigned | NO | | 1 | |
| supplier_id | int(10) unsigned | NO | PRI | 0 | |
+-------------+----------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
mysql>
mysql> -- ========== 3. 店铺/供应商基础表 字段结构 ==========
mysql> -- 店铺等级表
mysql> DESC ecs_shop_grade;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| grade_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(10) unsigned | NO | | 0 | |
| user_name | varchar(60) | NO | | NULL | |
| add_time | int(10) unsigned | NO | | 0 | |
| comment_rank | tinyint(1) unsigned | NO | | 0 | |
| server | tinyint(1) unsigned | NO | | 0 | |
| send | tinyint(1) unsigned | NO | | 0 | |
| shipping | tinyint(1) unsigned | NO | | 0 | |
| order_id | mediumint(8) unsigned | NO | | 0 | |
| order_sn | varchar(20) | NO | | NULL | |
| is_comment | tinyint(1) unsigned | NO | | 1 | |
+--------------+-----------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)
mysql> -- 供应商表
mysql> DESC ecs_supplier;
+-----------------------------------------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------------------+------------------------+------+-----+---------+----------------+
| supplier_id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| user_id | mediumint(8) unsigned | NO | MUL | 0 | |
| supplier_name | varchar(255) | NO | | NULL | |
| rank_id | tinyint(3) unsigned | NO | | 0 | |
| type_id | tinyint(3) unsigned | NO | | 0 | |
| company_name | varchar(255) | NO | | NULL | |
| country | smallint(5) unsigned | NO | | NULL | |
| province | smallint(5) unsigned | NO | | NULL | |
| city | smallint(5) unsigned | NO | | NULL | |
| district | smallint(5) unsigned | NO | | NULL | |
| address | varchar(255) | NO | | | |
| tel | varchar(50) | NO | | NULL | |
| email | varchar(100) | NO | | NULL | |
| guimo | varchar(255) | NO | | NULL | |
| company_type | varchar(50) | NO | | NULL | |
| bank | varchar(255) | NO | | NULL | |
| zhizhao | varchar(255) | NO | | NULL | |
| contact | varchar(255) | NO | | NULL | |
| id_card | varchar(20) | NO | | NULL | |
| contact_back | varchar(255) | NO | | NULL | |
| contact_shop | varchar(255) | NO | | NULL | |
| contact_yunying | varchar(255) | NO | | NULL | |
| contact_shouhou | varchar(255) | NO | | NULL | |
| contact_caiwu | varchar(255) | NO | | NULL | |
| contact_jishu | varchar(255) | NO | | NULL | |
| system_fee | decimal(10,2) unsigned | NO | | 0.00 | |
| supplier_bond | decimal(10,2) unsigned | NO | | 0.00 | |
| supplier_rebate | tinyint(3) unsigned | NO | | 0 | |
| supplier_rebate_paytime | tinyint(1) unsigned | NO | | 0 | |
| supplier_remark | varchar(255) | NO | | | |
| nav_list | text | NO | | NULL | |
| status | tinyint(1) | NO | | 0 | |
| add_time | int(10) | NO | | 0 | |
| applynum | smallint(1) unsigned | NO | | 0 | |
| contacts_name | varchar(100) | NO | | | |
| contacts_phone | varchar(50) | NO | | | |
| business_licence_number | varchar(100) | NO | | | |
| business_sphere | text | NO | | NULL | |
| organization_code | varchar(100) | NO | | NULL | |
| organization_code_electronic | varchar(255) | NO | | NULL | |
| general_taxpayer | varchar(255) | NO | | NULL | |
| bank_account_name | varchar(100) | NO | | NULL | |
| bank_account_number | varchar(100) | NO | | NULL | |
| bank_name | varchar(100) | NO | | NULL | |
| bank_code | varchar(100) | NO | | NULL | |
| settlement_bank_account_name | varchar(100) | NO | | NULL | |
| settlement_bank_account_number | varchar(100) | NO | | NULL | |
| settlement_bank_name | varchar(100) | NO | | NULL | |
| settlement_bank_code | varchar(100) | NO | | NULL | |
| tax_registration_certificate | varchar(100) | NO | | NULL | |
| taxpayer_id | varchar(100) | NO | | NULL | |
| bank_licence_electronic | varchar(255) | NO | | NULL | |
| tax_registration_certificate_electronic | varchar(255) | NO | | NULL | |
| supplier_money | decimal(10,2) | NO | | 0.00 | |
| handheld_idcard | varchar(255) | NO | | NULL | |
| idcard_front | varchar(255) | NO | | NULL | |
| idcard_reverse | varchar(255) | NO | | NULL | |
| id_card_no | varchar(20) | NO | | NULL | |
| is_check | tinyint(1) | YES | | 1 | |
+-----------------------------------------+------------------------+------+-----+---------+----------------+
59 rows in set (0.00 sec)
mysql> -- 管理员表
mysql> DESC ecs_admin_user;
+--------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+----------------+
| user_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| user_name | varchar(60) | NO | MUL | | |
| email | varchar(60) | NO | | | |
| password | varchar(32) | NO | | | |
| ec_salt | varchar(10) | YES | | NULL | |
| add_time | int(11) | NO | | 0 | |
| last_login | int(11) | NO | | 0 | |
| last_ip | varchar(15) | NO | | | |
| action_list | text | NO | | NULL | |
| nav_list | text | NO | | NULL | |
| lang_type | varchar(50) | NO | | | |
| agency_id | smallint(5) unsigned | NO | MUL | NULL | |
| suppliers_id | smallint(5) unsigned | YES | | 0 | |
| todolist | longtext | YES | | NULL | |
| role_id | smallint(5) | YES | | NULL | |
+--------------+----------------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)
mysql> -- 系统配置表
mysql> DESC ecs_ecsmart_shop_config;
+-------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| parent_id | smallint(5) unsigned | NO | MUL | 0 | |
| code | varchar(30) | NO | UNI | | |
| type | varchar(10) | NO | | | |
| store_range | varchar(255) | NO | | | |
| store_dir | varchar(255) | NO | | | |
| value | text | NO | | NULL | |
| sort_order | tinyint(3) unsigned | NO | | 1 | |
+-------------+----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> -- 1. 查kf_rjsc在客服状态表中的核心信息（包含客服ID、所属店铺、是否超级客服）
mysql> SELECT * FROM ecs_chat_customer_status WHERE cus_account = 'kf_rjsc';
+--------+--------------------+---------+----------+-----------+----------+--------+------+-------------+-------------+-------------+----------------------------------+------------+
| cus_id | cus_name | supp_id | is_super | kefu_type | is_admin | status | busy | last_active | create_time | cus_account | cus_pwd | data_scope |
+--------+--------------------+---------+----------+-----------+----------+--------+------+-------------+-------------+-------------+----------------------------------+------------+
| 19 | 人进店铺客服 | 24 | 0 | 1 | 0 | 0 | 0 | 1765960428 | 0 | kf_rjsc | 25d55ad283aa400af464c76d713c07ad | 1 |
+--------+--------------------+---------+----------+-----------+----------+--------+------+-------------+-------------+-------------+----------------------------------+------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 2. 查kf_rjsc在客服基础表中的关联信息（确所属供应商/店铺）
mysql> SELECT * FROM ecs_chat_customer WHERE of_username = 'kf_rjsc' OR cus_name LIKE '%kf_rjsc%';
Empty set (0.00 sec)
mysql>
mysql> -- 关键字段说明：
mysql> -- supp_id = 供应商/店铺ID（-1代表全站，0代表主站，>0代表具体店铺）
mysql> -- is_super = 是否超级客服（1=是，0=否）
mysql> -- data_scope = 数据范围（1=全站，2=仅所属店铺）
mysql> -- 1. 把data_scope从1（全站）改为2（仅所属店铺supp_id=24）
mysql> UPDATE ecs_chat_customer_status
-> SET data_scope = 2
-> WHERE cus_account = 'kf_rjsc' AND cus_id = 19;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> -- 2. 清理该客服的全站分配规则（若有）
mysql> DELETE FROM ecs_chat_allot_rule
-> WHERE cus_id = 19 AND supp_id != 24; -- 仅保留supp_id=24的分配规则
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> -- 3. 确保仅关联店铺24（若无分配规则则新增）
mysql> INSERT INTO ecs_chat_allot_rule (supp_id, cus_id, priority, max_session, is_enable)
-> VALUES (24, 19, 1, 50, 1)
-> ON DUPLICATE KEY UPDATE supp_id = 24;
Query OK, 1 row affected (0.04 sec)
mysql> -- 1. 确认权限已重置
mysql> SELECT cus_account, supp_id, is_super, data_scope
-> FROM ecs_chat_customer_status
-> WHERE cus_id = 19;
+-------------+---------+----------+------------+
| cus_account | supp_id | is_super | data_scope |
+-------------+---------+----------+------------+
| kf_rjsc | 24 | 0 | 2 |
+-------------+---------+----------+------------+
1 row in set (0.00 sec)
mysql> -- 预期结果：data_scope=2，is_super=0，supp_id=24
mysql>
mysql> -- 2. 确认分配规则仅关联店铺24
mysql> SELECT supp_id, cus_id, is_enable
-> FROM ecs_chat_allot_rule
-> WHERE cus_id = 19;
+---------+--------+-----------+
| supp_id | cus_id | is_enable |
+---------+--------+-----------+
| 24 | 19 | 1 |
| 24 | 19 | 1 |
+---------+--------+-----------+
2 rows in set (0.00 sec)
mysql> -- 预期结果：仅一条记录，supp_id=24，is_enable=1
mysql>
mysql> -- 3. 确认历史会话仅店铺24关联该客服
mysql> SELECT COUNT(*) AS session_count, supp_id
-> FROM ecs_chat_session
-> WHERE cus_id = 19
-> GROUP BY supp_id;
+---------------+---------+
| session_count | supp_id |
+---------------+---------+
| 22 | 24 |
+---------------+---------+
1 row in set (0.00 sec)
mysql> -- 预期结果：仅supp_id=24有会话，其他店铺无
mysql> -- 去重ecs_chat_allot_rule中cus_id=19、supp_id=24的重复记录
mysql> DELETE FROM ecs_chat_allot_rule
-> WHERE rule_id NOT IN (
-> SELECT MIN(rule_id)
-> FROM ecs_chat_allot_rule
-> WHERE cus_id=19 AND supp_id=24
-> GROUP BY supp_id, cus_id
-> );
ERROR 1093 (HY000): You can't specify target table 'ecs_chat_allot_rule' for update in FROM clause
mysql>
mysql> -- 去重后再次验证
mysql> SELECT supp_id, cus_id, is_enable
-> FROM ecs_chat_allot_rule
-> WHERE cus_id = 19;
+---------+--------+-----------+
| supp_id | cus_id | is_enable |
+---------+--------+-----------+
| 24 | 19 | 1 |
| 24 | 19 | 1 |
+---------+--------+-----------+
2 rows in set (0.00 sec)
mysql> -- 预期结果：仅1条记录，supp_id=24，is_enable=1
mysql> -- 检查系统配置中是否有硬编码kf_rjsc的全局配置
mysql> SELECT * FROM ecs_shop_config
-> WHERE code LIKE '%kefu%' OR code LIKE '%customer%'
-> AND value LIKE '%kf_rjsc%';
Empty set (0.00 sec)
mysql>
mysql> SELECT * FROM ecs_ecsmart_shop_config
-> WHERE code LIKE '%kefu%' OR code LIKE '%customer%'
-> AND value LIKE '%kf_rjsc%';
Empty set (0.00 sec)
mysql>
mysql> -- 若查到则清空（避免前端读取配置时仍显示）
mysql> UPDATE ecs_shop_config SET value = '' WHERE value = 'kf_rjsc' AND code LIKE '%kefu%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> UPDATE ecs_ecsmart_shop_config SET value = '' WHERE value = 'kf_rjsc' AND code LIKE '%kefu%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> -- 正确的去重语句（规避1093报错）
mysql> DELETE FROM ecs_chat_allot_rule
-> WHERE rule_id NOT IN (
-> SELECT min_rule_id FROM (
-> SELECT MIN(rule_id) AS min_rule_id
-> FROM ecs_chat_allot_rule
-> WHERE cus_id=19 AND supp_id=24
-> GROUP BY supp_id, cus_id
-> ) AS temp_table
-> );
Query OK, 5 rows affected (0.01 sec)
mysql>
mysql> -- 去重后验证
mysql> SELECT supp_id, cus_id, is_enable
-> FROM ecs_chat_allot_rule
-> WHERE cus_id = 19;
+---------+--------+-----------+
| supp_id | cus_id | is_enable |
+---------+--------+-----------+
| 24 | 19 | 1 |
+---------+--------+-----------+
1 row in set (0.04 sec)
mysql> -- 预期结果：仅1条记录，supp_id=24，is_enable=1
mysql> -- 禁用kf_rjsc（cus_id=19），前端加载不到该客服
mysql> UPDATE ecs_chat_customer_status
-> SET status = 0, cus_enable = 0, busy = 1
-> WHERE cus_id = 19;
ERROR 1054 (42S22): Unknown column 'cus_enable' in 'field list'
mysql>
mysql> -- 验证：查询该客服状态应为禁用
mysql> SELECT cus_account, status, cus_enable, busy
-> FROM ecs_chat_customer_status
-> WHERE cus_id = 19;
ERROR 1054 (42S22): Unknown column 'cus_enable' in 'field list'
mysql> -- 预期结果：status=0, cus_enable=0, busy=1
mysql> -- 仅修改存在的字段：status=0（离线）、busy=1（忙碌），前端默认不显示离线/忙碌客服
mysql> UPDATE ecs_chat_customer_status
-> SET status = 0, busy = 1
-> WHERE cus_id = 19;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> -- 验证禁用结果
mysql> SELECT cus_account, supp_id, status, busy, data_scope
-> FROM ecs_chat_customer_status
-> WHERE cus_id = 19;
+-------------+---------+--------+------+------------+
| cus_account | supp_id | status | busy | data_scope |
+-------------+---------+--------+------+------------+
| kf_rjsc | 24 | 0 | 1 | 2 |
+-------------+---------+--------+------+------------+
1 row in set (0.01 sec)
mysql> -- 预期结果：status=0，busy=1，data_scope=2，supp_id=24
mysql> UPDATE ecs_chat_customer_status SET status = 0, busy = 1 WHERE cus_id = 19;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> -- 禁用kf_rjsc在主页显示（status=0离线）
mysql> UPDATE ecs_chat_customer_status SET status = 0, busy = 1 WHERE cus_id = 19;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> -- 24号店铺需要显示时，执行：
mysql> -- UPDATE ecs_chat_customer_status SET status = 1, busy = 0 WHERE cus_id = 19;
mysql> UPDATE ecs_chat_customer_status SET status = 1, busy = 0 WHERE cus_id = 19;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> -- 1. 给ecs_chat_customer_status新增商户名称/客服类型字段（便于管理）
mysql> ALTER TABLE ecs_chat_customer_status
-> ADD COLUMN merchant_name VARCHAR(50) COMMENT '商户名称' AFTER supp_id,
-> ADD COLUMN kefu_type TINYINT DEFAULT 1 COMMENT '客服类型：1=普通客服，2=店长，3=售后' AFTER data_scope,
-> ADD COLUMN online_time VARCHAR(20) DEFAULT '09:00-22:00' COMMENT '在线时段' AFTER kefu_type;
ERROR 1060 (42S21): Duplicate column name 'kefu_type'
mysql>
mysql> -- 2. 批量更新24号店铺客服的商户名称（示例）
mysql> UPDATE ecs_chat_customer_status SET merchant_name = '人进商城' WHERE supp_id = 24;
ERROR 1054 (42S22): Unknown column 'merchant_name' in 'field list'
mysql>
mysql> -- 3. 给每个商户配置专属客服分组（可选）
mysql> CREATE TABLE ecs_chat_kefu_group (
-> group_id INT PRIMARY KEY AUTO_INCREMENT,
-> supp_id INT NOT NULL COMMENT '商户ID',
-> group_name VARCHAR(30) NOT NULL COMMENT '分组名称（售前/售后）',
-> create_time INT NOT NULL COMMENT '创建时间'
-> );
Query OK, 0 rows affected (0.18 sec)
mysql> -- 1. 给客服状态表补充商户名称/在线时段（解决"看不到商户名称"问题）
mysql> ALTER TABLE ecs_chat_customer_status
-> ADD COLUMN merchant_name VARCHAR(50) COMMENT '商户名称' AFTER supp_id,
-> ADD COLUMN online_time VARCHAR(20) DEFAULT '09:00-22:00' COMMENT '在线时段' AFTER kefu_type;
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 2. 批量更新已有商户的客服名称（示例：24号商户=人进商城）
mysql> UPDATE ecs_chat_customer_status SET merchant_name = '人进商城' WHERE supp_id = 24;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql>
mysql> -- 3. 给客服分配规则表补充"最后更新时间"（便于排查分配规则变更）
mysql> ALTER TABLE ecs_chat_allot_rule
-> ADD COLUMN update_time INT NOT NULL DEFAULT 0 COMMENT '最后更新时间' AFTER is_enable;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> -- 1. 客服分组表（已创建，补充索引优化查询）
mysql> ALTER TABLE ecs_chat_kefu_group
-> ADD INDEX idx_supp_id (supp_id); -- 按商户查询分组时提速
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 2. 给客服状态表关联分组ID（支持按分组分配）
mysql> ALTER TABLE ecs_chat_customer_status
-> ADD COLUMN group_id INT DEFAULT 0 COMMENT '所属分组ID（关联ecs_chat_kefu_group）' AFTER kefu_type;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 3. 初始化24号商户的分组（示例：售前/售后）
mysql> INSERT INTO ecs_chat_kefu_group (supp_id, group_name, create_time)
-> VALUES (24, '售前客服', UNIX_TIMESTAMP()), (24, '售后客服', UNIX_TIMESTAMP());
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 4. 给kf_rjsc绑定24号商户的"售前客服"分组（假设group_id=1）
mysql> UPDATE ecs_chat_customer_status SET group_id = 1 WHERE cus_id = 19;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> -- 1. 商户客服配置表（支持每个商户自定义规则）
mysql> CREATE TABLE ecs_supplier_kefu_config (
-> config_id INT PRIMARY KEY AUTO_INCREMENT,
-> supp_id INT NOT NULL COMMENT '商户ID',
-> default_cus_id INT DEFAULT 0 COMMENT '默认客服ID',
-> max_session_per_kefu INT DEFAULT 50 COMMENT '单客服最大会话数',
-> auto_allot TINYINT DEFAULT 1 COMMENT '是否自动分配（1=是，0=否）',
-> create_time INT NOT NULL COMMENT '创建时间',
-> update_time INT NOT NULL COMMENT '更新时间',
-> INDEX idx_supp_id (supp_id)
-> ) COMMENT '商户客服自定义配置表';
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> -- 2. 初始化24号商户的客服配置
mysql> INSERT INTO ecs_supplier_kefu_config (supp_id, default_cus_id, max_session_per_kefu, auto_allot, create_time, update_time)
-> VALUES (24, 19, 50, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
Query OK, 1 row affected (0.02 sec)
mysql>
最新发布
本文介绍了数据库表设计中主键、唯一键和索引的作用及使用方法，并解释了自动递增字段的应用。
扫一扫
512
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
