ERROR 3140

ERROR 3140

MySQL5.7 supports a native JSON data type, and how to use it?

数据结构与算法网站管理员 Published the article • 0 comments • 135 views • 2019-07-11 15:52 • 来自相关话题

mysql5.7已经支持JSON的数据结构,意味着我们可以更加方便的对JSON结构的数据进行增删改成

### 什么是JSON?

> JSON: JavaScript Object Notation(JavaScript 对象表示法)
> JSON 是存储和交换文本信息的语法。类似 XML。
> JSON 比 XML 更小、更快,更易解析。
> JSON 具有自我描述性,更易理解

### 建立表,添加JSON的数据结构字段col

建表qtest,然后添加col的字段,其数据类型为JSON,插入一条JSON字符串,然后发现添加不是json结构的数据
mysql会给我们报如下错误```ERROR 3140 (22032) at line 2: Invalid JSON text:```,表明我们插入的是非法JSON。

```json
{"mascot": "Our mascot is a dolphin named \"Sakila\"."}
```

```mysql
mysql> CREATE TABLE qtest (col JSON);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES('{"mascot": "Our mascot is a dolphin named \"Sakila\"."}');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text:
"Invalid value." at position 6 in value (or column) '[1, 2,'.
```

### 如何查询JSON的某个字段

1. 字段指向一个字符串,由```$.name```组成,美元符号+字段名称

```mysql
mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot" |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)
```

2. 查询数组```$[0]```,美元符号+中括号及数组索引

```mysql
mysql> select *from sourcedev;
+----+------------------------------+-----------+
| id | category | tags |
+----+------------------------------+-----------+
| 1 | {"id": 1, "name": "sourcedev.cc"} | [1, 2, 3] |
+----+------------------------------+-----------+
1 row in set (0.00 sec)

mysql> select id, category->'$.id', tags->'$[0]' from sourcedev;
+----+------------------+--------------+
| id | category->'$.id' | tags->'$[0]' |
+----+------------------+--------------+
| 1 | 1 | 1 |
+----+------------------+--------------+
1 row in set (0.00 sec)
```

### mysql5.7 JSON相关函数

1. JSON_TYPE JSON类型,ARRAY、STRING
所有的Json type类型如下
```
BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL
```

2. JSON_ARRAY ```SELECT JSON_ARRAY('a', 1, NOW());```

```mysql
mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW()) |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+
```

3. JSON_OBJECT
```mysql
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"} |
+---------------------------------------+
```
4. 等等


### 引用

1. [百度百科 #https://baike.baidu.com/item/JSON/2462549](https://baike.baidu.com/item/JSON/2462549)
2. [mysql5.7官方文档 #https://dev.mysql.com/doc/refman/8.0/en/json.html](https://dev.mysql.com/doc/refman/8.0/en/json.html) 查看全部

mysql5.7已经支持JSON的数据结构,意味着我们可以更加方便的对JSON结构的数据进行增删改成

### 什么是JSON?

> JSON: JavaScript Object Notation(JavaScript 对象表示法)
> JSON 是存储和交换文本信息的语法。类似 XML。
> JSON 比 XML 更小、更快,更易解析。
> JSON 具有自我描述性,更易理解

### 建立表,添加JSON的数据结构字段col

建表qtest,然后添加col的字段,其数据类型为JSON,插入一条JSON字符串,然后发现添加不是json结构的数据
mysql会给我们报如下错误```ERROR 3140 (22032) at line 2: Invalid JSON text:```,表明我们插入的是非法JSON。

```json
{"mascot": "Our mascot is a dolphin named \"Sakila\"."}
```

```mysql
mysql> CREATE TABLE qtest (col JSON);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES('{"mascot": "Our mascot is a dolphin named \"Sakila\"."}');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text:
"Invalid value." at position 6 in value (or column) '[1, 2,'.
```

### 如何查询JSON的某个字段

1. 字段指向一个字符串,由```$.name```组成,美元符号+字段名称

```mysql
mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot" |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)
```

2. 查询数组```$[0]```,美元符号+中括号及数组索引

```mysql
mysql> select *from sourcedev;
+----+------------------------------+-----------+
| id | category | tags |
+----+------------------------------+-----------+
| 1 | {"id": 1, "name": "sourcedev.cc"} | [1, 2, 3] |
+----+------------------------------+-----------+
1 row in set (0.00 sec)

mysql> select id, category->'$.id', tags->'$[0]' from sourcedev;
+----+------------------+--------------+
| id | category->'$.id' | tags->'$[0]' |
+----+------------------+--------------+
| 1 | 1 | 1 |
+----+------------------+--------------+
1 row in set (0.00 sec)
```

### mysql5.7 JSON相关函数

1. JSON_TYPE JSON类型,ARRAY、STRING
所有的Json type类型如下
```
BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL
```

2. JSON_ARRAY ```SELECT JSON_ARRAY('a', 1, NOW());```

```mysql
mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW()) |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+
```

3. JSON_OBJECT
```mysql
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"} |
+---------------------------------------+
```
4. 等等


### 引用

1. [百度百科 #https://baike.baidu.com/item/JSON/2462549](https://baike.baidu.com/item/JSON/2462549)
2. [mysql5.7官方文档 #https://dev.mysql.com/doc/refman/8.0/en/json.html](https://dev.mysql.com/doc/refman/8.0/en/json.html)


MySQL5.7 supports a native JSON data type, and how to use it?

数据结构与算法网站管理员 Published the article • 0 comments • 135 views • 2019-07-11 15:52 • 来自相关话题

mysql5.7已经支持JSON的数据结构,意味着我们可以更加方便的对JSON结构的数据进行增删改成

### 什么是JSON?

> JSON: JavaScript Object Notation(JavaScript 对象表示法)
> JSON 是存储和交换文本信息的语法。类似 XML。
> JSON 比 XML 更小、更快,更易解析。
> JSON 具有自我描述性,更易理解

### 建立表,添加JSON的数据结构字段col

建表qtest,然后添加col的字段,其数据类型为JSON,插入一条JSON字符串,然后发现添加不是json结构的数据
mysql会给我们报如下错误```ERROR 3140 (22032) at line 2: Invalid JSON text:```,表明我们插入的是非法JSON。

```json
{"mascot": "Our mascot is a dolphin named \"Sakila\"."}
```

```mysql
mysql> CREATE TABLE qtest (col JSON);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES('{"mascot": "Our mascot is a dolphin named \"Sakila\"."}');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text:
"Invalid value." at position 6 in value (or column) '[1, 2,'.
```

### 如何查询JSON的某个字段

1. 字段指向一个字符串,由```$.name```组成,美元符号+字段名称

```mysql
mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot" |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)
```

2. 查询数组```$[0]```,美元符号+中括号及数组索引

```mysql
mysql> select *from sourcedev;
+----+------------------------------+-----------+
| id | category | tags |
+----+------------------------------+-----------+
| 1 | {"id": 1, "name": "sourcedev.cc"} | [1, 2, 3] |
+----+------------------------------+-----------+
1 row in set (0.00 sec)

mysql> select id, category->'$.id', tags->'$[0]' from sourcedev;
+----+------------------+--------------+
| id | category->'$.id' | tags->'$[0]' |
+----+------------------+--------------+
| 1 | 1 | 1 |
+----+------------------+--------------+
1 row in set (0.00 sec)
```

### mysql5.7 JSON相关函数

1. JSON_TYPE JSON类型,ARRAY、STRING
所有的Json type类型如下
```
BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL
```

2. JSON_ARRAY ```SELECT JSON_ARRAY('a', 1, NOW());```

```mysql
mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW()) |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+
```

3. JSON_OBJECT
```mysql
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"} |
+---------------------------------------+
```
4. 等等


### 引用

1. [百度百科 #https://baike.baidu.com/item/JSON/2462549](https://baike.baidu.com/item/JSON/2462549)
2. [mysql5.7官方文档 #https://dev.mysql.com/doc/refman/8.0/en/json.html](https://dev.mysql.com/doc/refman/8.0/en/json.html) 查看全部

mysql5.7已经支持JSON的数据结构,意味着我们可以更加方便的对JSON结构的数据进行增删改成

### 什么是JSON?

> JSON: JavaScript Object Notation(JavaScript 对象表示法)
> JSON 是存储和交换文本信息的语法。类似 XML。
> JSON 比 XML 更小、更快,更易解析。
> JSON 具有自我描述性,更易理解

### 建立表,添加JSON的数据结构字段col

建表qtest,然后添加col的字段,其数据类型为JSON,插入一条JSON字符串,然后发现添加不是json结构的数据
mysql会给我们报如下错误```ERROR 3140 (22032) at line 2: Invalid JSON text:```,表明我们插入的是非法JSON。

```json
{"mascot": "Our mascot is a dolphin named \"Sakila\"."}
```

```mysql
mysql> CREATE TABLE qtest (col JSON);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES('{"mascot": "Our mascot is a dolphin named \"Sakila\"."}');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text:
"Invalid value." at position 6 in value (or column) '[1, 2,'.
```

### 如何查询JSON的某个字段

1. 字段指向一个字符串,由```$.name```组成,美元符号+字段名称

```mysql
mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot" |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)
```

2. 查询数组```$[0]```,美元符号+中括号及数组索引

```mysql
mysql> select *from sourcedev;
+----+------------------------------+-----------+
| id | category | tags |
+----+------------------------------+-----------+
| 1 | {"id": 1, "name": "sourcedev.cc"} | [1, 2, 3] |
+----+------------------------------+-----------+
1 row in set (0.00 sec)

mysql> select id, category->'$.id', tags->'$[0]' from sourcedev;
+----+------------------+--------------+
| id | category->'$.id' | tags->'$[0]' |
+----+------------------+--------------+
| 1 | 1 | 1 |
+----+------------------+--------------+
1 row in set (0.00 sec)
```

### mysql5.7 JSON相关函数

1. JSON_TYPE JSON类型,ARRAY、STRING
所有的Json type类型如下
```
BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL
```

2. JSON_ARRAY ```SELECT JSON_ARRAY('a', 1, NOW());```

```mysql
mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW()) |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+
```

3. JSON_OBJECT
```mysql
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"} |
+---------------------------------------+
```
4. 等等


### 引用

1. [百度百科 #https://baike.baidu.com/item/JSON/2462549](https://baike.baidu.com/item/JSON/2462549)
2. [mysql5.7官方文档 #https://dev.mysql.com/doc/refman/8.0/en/json.html](https://dev.mysql.com/doc/refman/8.0/en/json.html)