mysql 5.7

mysql

说明：使用json我发现并没有比我们现在变快多少，json虽然很方便的查询各种结果，也很方便的增删改查指定的json字段，不过我个人感觉现在还不适用

1，安装

具体安装我是下载rpm文件 安装 选择小红帽版本然后执行

yum localinstall mysql-community-server-5.7.13-1.el6.x86_64.rpm

第一次安装完5.7版本后需要修改密码，因为5.7版本密码是随机生成的，非空
修改如下

service mysqld stop//关闭服务
mysqld_safe --skip-grant-tables &  //安全模式
mysql  -uroot //进入mysql
update mysql.user set
authentication_string=password('123456') where user='root' and Host = 'localhost';#修改密码
flush privileges;
service mysqld restart;//重启

新版本可以设置用户密码过期时间 例如30天

ALTER USER 'root'@localhost' PASSWORD EXPIRE INTERVAL 30 DAYS;

我们测试就不用过期了直接永久有效

ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER; //设置密码无限期

2，chat数据库导出

mysqldump  --routines --default-character-set=utf8 --databases j_chat > j_chat_db.sql
mysql  --default-character-set=utf8 < j_chat_db.sql

修改之前数据测试 j_chat.msg_chat 共计聊天数据247.5275万条

mysql> select count(*) from chat_msg ;
+----------+
| count(*) |
+----------+
|  2475275 |
+----------+
1 row in set (3.66 sec)
mysql> select count(*) from chat_msg  where data like '%fromUser":10109,%';
+----------+
| count(*) |
+----------+
|     2156 |
+----------+
1 row in set (7.13 sec)

3，修改成json列

直接alter即可 也没发现神马意外情况

ALTER TABLE j_chat.chat_msg MODIFY data json;

然后在查询数据

如果用like 会比以前慢3倍多 ,用json列查询性能差不多，但是还是要慢一点

mysql>  select count(*) from chat_msg ;
+----------+
| count(*) |
+----------+
|  2475275 |
+----------+
1 row in set (3.68 sec)
mysql> select count(*) from chat_msg  where data like '%fromUser":10109,%';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (23.92 sec)
mysql> select count(*) from chat_msg  where json_extract(data,'$.fromUser') = 10109;
+----------+
| count(*) |
+----------+
|     2156 |
+----------+
1 row in set (8.39 sec)

4，其他json命令

获取json列里面keys

mysql> SELECT id,json_keys(data) as "keys" FROM chat_msg order by id desc limit 1\G;
*************************** 1. row ***************************
  id: 2475276
keys: ["ip", "ua", "type", "appVer", "toUser", "channel", "deviceId", "fromUser", "platform", "deviceMac", "devilEmotionId", "devilEmotionSize"]
1 row in set (0.00 sec)

由json field组建成新的结果集

SELECT json_extract(data,'$.fromUser') as fromUser, json_extract(data,'$.toUser') as toUser ,json_extract(data,'$.type') as type  from chat_msg order by id desc limit 10;
+----------+--------+------+
| fromUser | toUser | type |
+----------+--------+------+
| 10109    | 10090  | 14   |
| 10109    | 10090  | 14   |
| 10109    | 10090  | 14   |
| 10109    | 10090  | 14   |
| 10109    | 10090  | 7    |
| 10109    | 10090  | 7    |
| 10109    | 10090  | 7    |
| 10109    | 10090  | 6    |
| 10109    | 10090  | 7    |
| 10109    | 10090  | 14   |
+----------+--------+------+
10 rows in set (0.00 sec)

另外一种简化写法

mysql> SELECT data->'$.fromUser' as fromUser,data->'$.toUser' as toUser ,data->'$.type' as type  from chat_msg order by id desc limit 10;
+----------+--------+------+
| fromUser | toUser | type |
+----------+--------+------+
| 10109    | 10090  | 14   |
| 10109    | 10090  | 14   |
| 10109    | 10090  | 14   |
| 10109    | 10090  | 14   |
| 10109    | 10090  | 7    |
| 10109    | 10090  | 7    |
| 10109    | 10090  | 7    |
| 10109    | 10090  | 6    |
| 10109    | 10090  | 7    |
| 10109    | 10090  | 14   |
+----------+--------+------+
10 rows in set (0.00 sec)

移除json中某一个字段 json_remove

mysql> select * from chat_msg where id=4\G;
*************************** 1. row ***************************
         id: 4
       data: {"ua": "Wuya 0.9.9.99999 (Android (19/4.4.2; 320dpi; 720x1280; HUAWEI/Huawei; H30-U10; hwH30-U10; mt6582))", "type": 1, "appVer": "0.9.9", "toUser": 1, "channel": 99999, "content": "?", "deviceId": "f0d53964038835b0265b1c17e69f8c07", "fromUser": 4, "platform": 1, "deviceMac": "88:e3:ab:bf:ad:83"}
     status: 0
create_time: 1429867905558
1 row in set (0.00 sec)
UPDATE chat_msg  SET data = json_remove(data,'$.ua') where  id=4; 
mysql> select * from chat_msg where id=4\G;
*************************** 1. row ***************************
         id: 4
       data: {"type": 1, "appVer": "0.9.9", "toUser": 1, "channel": 99999, "content": "?", "deviceId": "f0d53964038835b0265b1c17e69f8c07", "fromUser": 4, "platform": 1, "deviceMac": "88:e3:ab:bf:ad:83"}
     status: 0
create_time: 1429867905558
1 row in set (0.00 sec)

另外还有 JSON_APPEND JSON_MERGE
@see 官方网站其他命令

内网测试环境

10.10.5.10
密码1234
mysql 没密码