mysql - 在虚拟列上创建 mysql 索引的问题
问题描述
我有一个 Mysql 表“事件”,它有一个虚拟列“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 }');
谢谢
解决方案
边注:
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';
我在想"null"
JSON 中某处有 4 个字母的字符串。
(评论后)
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 的方法。
推荐阅读
- php - PHP - 从数据创建新数组并对重复键中的数据求和
- r - 在循环内部而不是外部运行时出现无效时间争论错误
- python - Python TFLearn 无法保存到单个 model.tfl 文件
- vue.js - 下拉选择后Vue启用文本字段
- uiviewcontroller - 使用 UIHostingController 的 UIScrollView 跳跃视图
- python - 为什么我的运行部分显示“AttributeError:'numpy.ndarray'对象没有属性'load'”,即使我没有写“load”?
- reactjs - 为什么我的页面加载超过 1.5 秒,而 next.js 中使用棱镜的数据很少?
- r - Find the ratio between each column entry and the following entry, for every column, for a list of data.frames in R
- c - C套接字下载功能传输损坏的文件
- vba - 未应用条件格式