mysql 5.7 JSON 支持

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

mysql> show variables like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

2.创建数据库

mysql> create database taobao;
Query OK, 1 row affected (0.00 sec)

mysql> show create database taobao;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| taobao      | CREATE DATABASE `taobao` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

3.创建表

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

4.插入数据

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

5.查询数据

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

评论

暂无评论