mysql 5.7 JSON 支持

1.首先要保证正确的字符集

1mysql> show variables like 'char%';
2+--------------------------+----------------------------------+
3| Variable_name            | Value                            |
4+--------------------------+----------------------------------+
5| character_set_client     | utf8                             |
6| character_set_connection | utf8                             |
7| character_set_database   | utf8                             |
8| character_set_filesystem | binary                           |
9| character_set_results    | utf8                             |
10| character_set_server     | utf8                             |
11| character_set_system     | utf8                             |
12| character_sets_dir       | /usr/local/mysql/share/charsets/ |
13+--------------------------+----------------------------------+
148 rows in set (0.01 sec)

2.创建数据库

1mysql> create database taobao;
2Query OK, 1 row affected (0.00 sec)
3
4mysql> show create database taobao;
5+----------+--------------------------------------------------------------+
6| Database | Create Database                                              |
7+----------+--------------------------------------------------------------+
8| taobao      | CREATE DATABASE `taobao` /*!40100 DEFAULT CHARACTER SET utf8 */ |
9+----------+--------------------------------------------------------------+
101 row in set (0.00 sec)

3.创建表

1CREATE TABLE `test` (
2    ->   `uid` int(11) NOT NULL AUTO_INCREMENT,
3    ->   `data` json DEFAULT NULL,
4    ->   PRIMARY KEY (`uid`)
5    -> ) ENGINE=InnoDB AUTO_INCREMENT=131 DEFAULT CHARSET=utf8;
6Query OK, 0 rows affected (0.02 sec)

4.插入数据

1mysql> insert  into  test (data)  values(json_object("name","中华人民共和国","birthday","2015-05-09"));
2Query OK, 1 row affected (0.01 sec)
3

5.查询数据

1mysql> SELECT JSON_UNQUOTE(data) as data_value FROM test;
2+-------------------------------------------------------------+
3| data_value                                                  |
4+-------------------------------------------------------------+
5| {"name": "中华人民共和国", "birthday": "2015-05-09"}        |
6+-------------------------------------------------------------+
71 row in set (0.00 sec)