mysql - 查询MYSQL longtext持有JSON

Question

我有一个具有以下结构的表:

CREATE TABLE `Event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `created` datetime(6) NOT NULL,
  `last_updated` datetime(6) NOT NULL,
  `info` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=720 DEFAULT CHARSET=latin1

我需要使用 info 列(包含 JSON 字符串)来查询它。

如果我查询它:

SELECT e.id, e.created, JSON_TYPE(e.info) AS info
  FROM Event e

它在 INFO 列上返回 OBJECT，但如果我尝试获取 JSON 的特定列，我会收到:

SQL Error [3144] [22001]: Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'.

我正在运行的 SQL:

SELECT INFO - > '$User'
  FROM (SELECT e.id, e.created, JSON_TYPE(e.info) AS info
          FROM Event e) as X

将列的类型改为json不是一个选项

Answer 1

你可以试试CAST然后到 JSON

SELECT info -> '$.User', info_type
  FROM (SELECT e.id, e.created, 
               JSON_TYPE( CAST( info AS JSON ) ) AS info_type, 
               CAST( info AS JSON ) AS info
          FROM Event e) as X

因为 JSON 值接受函数 (JSON_TYPE()) 中的字符串值不起作用。

顺便说一句，您不需要为您的情况提供 JSON 类型，而只需提供 JSON。

更新:您也可以通过使用 JSON_EXTRACT() as

来替代使用子查询

SELECT JSON_EXTRACT( CAST( info AS JSON ) , '$.A' ) AS info,
       JSON_TYPE( CAST( info AS JSON ) ) AS info_type
  FROM Event