【 使用环境 】生产环境
【 OB or 其他组件 】 OB
【 使用版本 】4.0.0
【问题描述】mysql5.7迁移到OB后sql有兼容问题
【复现路径】insert 语句插入失败
CREATE TABLE bi_game_statistics (
date date NOT NULL COMMENT ‘时间’,
channel_id varchar(32) NOT NULL DEFAULT ‘’,
login_num bigint(20) NOT NULL DEFAULT ‘0’,
sign_in_num bigint(20) NOT NULL DEFAULT ‘0’,
sell_num bigint(20) NOT NULL DEFAULT ‘0’,
sell_amount bigint(20) NOT NULL DEFAULT ‘0’,
buy_num bigint(20) NOT NULL DEFAULT ‘0’,
buy_amount bigint(20) NOT NULL DEFAULT ‘0’,
steal_dish_num bigint(20) NOT NULL DEFAULT ‘0’,
daily_tasks_num bigint(20) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (date, channel_id),
KEY channel_id (channel_id) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0;
CREATE TABLE game_flow (
event_time int(11) DEFAULT NULL,
account_id bigint(20) DEFAULT NULL,
uid bigint(20) DEFAULT NULL,
channel_id varchar(20) DEFAULT NULL,
nick_name varchar(128) DEFAULT NULL,
event_code varchar(32) DEFAULT NULL,
value_1 varchar(20) DEFAULT NULL,
value_2 varchar(20) DEFAULT NULL,
value_3 varchar(20) DEFAULT NULL,
KEY idx_ix_event_time_986206507 (event_time) BLOCK_SIZE 16384 LOCAL,
KEY idx_ix_account_id_702253689 (account_id) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0;
执行语句
INSERT INTO data_bi.bi_game_statistics(date, channel_id, sell_num, sell_amount)
SELECT
“2022-12-06” date,
channel_id,
count(DISTINCT account_id),
sum(value_2)
FROM game_flow
WHERE event_time BETWEEN UNIX_TIMESTAMP(“2022-12-06”) AND UNIX_TIMESTAMP(“2022-12-06 23:59:59”)
AND event_code in(SELECT code from data_gamemanager.sys_dict where type =“sell_type”) and value_2 !=‘nil’ and value_2>0
GROUP BY channel_id
ON DUPLICATE KEY UPDATE sell_num=values(sell_num),sell_amount=values(sell_amount);
查询内容：
| date | channel_id | count(DISTINCT account_id) | sum(value_2) |
|---|---|---|---|
| 2022-12-06 | XXXXXXX | 31 | 3514400 |
| 2022-12-06 | XXXXXXX | 24 | 3322100 |
| 2022-12-06 | XXXXXXX | 7 | 1639000 |
| 2022-12-06 | XXXXXXX | 23 | 1396300 |
| 2022-12-06 | XXXXXXX | 91 | 6693600 |
| 2022-12-06 | XXXXXXX | 238 | 139175500 |
| 2022-12-06 | XXXXXXX | 371 | 49287300 |
| 2022-12-06 | XXXXXXX | 518 | 126376400 |
| 2022-12-06 | XXXXXXX | 814 | 219169300 |
| 2022-12-06 | XXXXXXX | 254 | 110291000 |
| 2022-12-06 | XXXXXXX | 283 | 18114400 |
| 2022-12-06 | XXXXXXX | 838 | 423710600 |
| 2022-12-06 | XXXXXXX | 36357 | 7309610500 |
| 2022-12-06 | XXXXXXX | 74388 | 6903668800 |
| 2022-12-06 | XXXXXXX | 8423 | 862011100 |
| 2022-12-06 | XXXXXXX | 1438 | 138956900 |
| 2022-12-06 | XXXXXXX | 503 | 31081900 |
| 2022-12-06 | XXXXXXX | 92 | 1469600 |
| 2022-12-06 | XXXXXXX | 2541 | 121012400 |
| 2022-12-06 | XXXXXXX | 717 | 123792500 |
| 2022-12-06 | XXXXXXX | 443 | 21581300 |
| 2022-12-06 | XXXXXXX | 3731 | 2210189700 |
| 2022-12-06 | XXXXXXX | 599 | 662656100 |
| 2022-12-06 | XXXXXXX | 867 | 551007100 |
| 2022-12-06 | XXXXXXX | 621 | 213107000 |
| 2022-12-06 | XXXXXXX | 106 | 110247300 |
| 2022-12-06 | XXXXXXX | 476 | 270083300 |
| 2022-12-06 | XXXXXXX | 718 | 124061200 |
| 2022-12-06 | XXXXXXX | 208 | 161970000 |
| 2022-12-06 | XXXXXXX | 216 | 157154000 |
| 2022-12-06 | XXXXXXX | 230 | 114275600 |
| 2022-12-06 | XXXXXXX | 8467 | 5498230800 |
【问题现象及影响】
【附件】
