mysql - 在虚拟列上创建 mysql 索引时出现问题

Question

我有一个 Mysql 表“Event”，它有一个虚拟列“campaignId”。我试图在此列上创建索引但没有成功。

当我尝试创建索引时:

ALTER TABLE `botbit`.`Event` 
ADD INDEX `IndexName` (`campaignId` ASC);

我收到这个错误

Error Code: 1366. Incorrect integer value: 'null' for column 'campaignId' at row 1

campaignId 列是虚拟的，其定义如下:

ALTER TABLE `botbit`.`Event` 
ADD COLUMN `campaignId` INT(11) GENERATED ALWAYS AS (case when (json_unquote(json_extract(`customProps`,'$.campaignId')) IS NOT NULL) then json_unquote(json_extract(`customProps`,'$.campaignId')) else -4000 end);

如果 json 属性 campaignId 不存在，则 campaignId 的值设置为 -4000(以避免空值)。

我还测试了该列中是否存在空值，但没有:

select * from Event where campaignId IS NULL LIMIT 1;

0 row(s) returned

如果我在该列中没有任何空值，我不明白为什么 mysql 会告诉我“不正确的值:列 campaignId 为空”。

我在其他虚拟列上有其他索引并且工作正常，即使存在 NULL 值也是如此。所以，我认为应该有一些我无法弄清楚的数据问题。

编辑:寻找字符串“空”值我得到了这个结果

SELECT id,campaignId FROM tbl
    WHERE json_unquote(json_extract(`customProps`,'$.campaignId'))
          = 'null';
| 21096314 |          0 |
| 21096315 |          0 |
| 21096316 |          0 |
| 21096317 |          0 |
| 21096318 |          0 |
| 21096319 |          0 |
| 21096320 |          0 |
| 21096321 |          0 |
| 21096322 |          0 |
| 21096323 |          0 |
| 21096324 |          0 |

编辑 2:创建表

CREATE TABLE `Event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` smallint(5) unsigned NOT NULL,
  `subType` smallint(5) unsigned DEFAULT NULL,
  `storeId` mediumint(9) NOT NULL,
  `userId` int(11) DEFAULT NULL,
  `source` smallint(5) unsigned NOT NULL DEFAULT '0',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `customProps` json DEFAULT NULL,
  `timestamp_i` int(11) DEFAULT NULL,
  `mac` varchar(17) COLLATE utf8mb4_bin GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.mac'))) VIRTUAL,
  `deviceId` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.deviceId'))) VIRTUAL,
  `poc` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.poc'))) VIRTUAL,
  `registeredThrough` varchar(45) COLLATE utf8mb4_bin GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.registeredThrough'))) VIRTUAL,
  `pointOfContactId` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.pointOfContactId'))) VIRTUAL,
  `ticket` decimal(10,0) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.ticket'))) VIRTUAL,
  `promoCodeId` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.promoCodeId'))) VIRTUAL,
  `date` date GENERATED ALWAYS AS (cast(`timestamp` as date)) VIRTUAL,
  `npsScore` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.score'))) VIRTUAL,
  `npsComment` varchar(2000) COLLATE utf8mb4_bin GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.comment'))) VIRTUAL,
  `campaignName` varchar(2000) COLLATE utf8mb4_bin GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.campaignName'))) VIRTUAL,
  `productId` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.productId'))) VIRTUAL,
  `reservationId` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.id'))) VIRTUAL,
  `campaignId` int(11) GENERATED ALWAYS AS ((case when (json_unquote(json_extract(`customProps`,'$.campaignId')) is not null) then json_unquote(json_extract(`customProps`,'$.campaignId')) else -(4000) end)) VIRTUAL,
  `isCustomCampaign` tinyint(1) GENERATED ALWAYS AS ((case when (json_unquote(json_extract(`customProps`,'$.isCustomCampaign')) = 'true') then 1 when (json_unquote(json_extract(`customProps`,'$.isCustomCampaign')) = 'false') then 0 else 0 end)) VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Event_id_uindex` (`id`),
  KEY `Event_userId_index` (`userId`),
  KEY `Event_subType_storeId_index` (`subType`,`storeId`),
  KEY `Event_timetamp_index` (`timestamp`),
  KEY `Event_subtype_storeId_userId_timestamp_index` (`subType`,`userId`,`storeId`,`timestamp`),
  KEY `Event_storeId_type` (`storeId`,`type`),
  KEY `Event_mac_index` (`mac`),
  KEY `Event_deviceId_index` (`deviceId`)
) ENGINE=InnoDB AUTO_INCREMENT=30693655 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

编辑 3:记录插入示例

INSERT INTO `Event` (`type`, `subType`, `storeId`, `userId`, `source`, `timestamp`, `customProps`) VALUES ('1', '16', '3', '1', '2', '2018-06-04 15:41:56', '{ \"campaignId\": 100, \"isCustomCampaign\": false }');

谢谢

Answer 1

边注:

ADD COLUMN `campaignId` INT(11)
    GENERATED ALWAYS AS (
        case when (json_unquote(json_extract(`customProps`,'$.campaignId')) IS NOT NULL)
             then  json_unquote(json_extract(`customProps`,'$.campaignId'))
             else -4000 end);

更简单:

ADD COLUMN `campaignId` INT(11)
    GENERATED ALWAYS AS (
        COALESCE(json_unquote(json_extract(`customProps`,'$.campaignId')),
                -4000)

至于问题，看你得到什么

SELECT * FROM tbl
    WHERE json_unquote(json_extract(`customProps`,'$.campaignId'))
          = 'null';

我认为 JSON 中某处有 4 个字母的字符串 "null"。

(评论后)

mysql> SET @j := '{ \"campaignId\": 100, \"isCustomCampaign\": false }';

SELECT json_unquote(json_extract(@j, '$.campaignId')) AS the_value,
       json_unquote(json_extract(@j, '$.campaignId')) = 'null' AS string_cmp,
       json_unquote(json_extract(@j, '$.campaignId')) IS NULL AS null_cmp,
       case when  (json_unquote(json_extract(@j, '$.campaignId')) IS NOT NULL)
             then  json_unquote(json_extract(@j, '$.campaignId'))
             else -4000 end  AS the_case;

+-----------+------------+----------+----------+
| the_value | string_cmp | null_cmp | the_case |
+-----------+------------+----------+----------+
| 100       |          0 |        0 | 100      |
+-----------+------------+----------+----------+

SET @j := '{  \"isCustomCampaign\": false }';
(then same query)

+-----------+------------+----------+----------+
| the_value | string_cmp | null_cmp | the_case |
+-----------+------------+----------+----------+
| NULL      |       NULL |        1 | -4000    |
+-----------+------------+----------+----------+

这是否为您提供了更多线索？也许它还为您提供了一种在不涉及大表的情况下试验 JSON 的方法。