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)