---
# System prepended metadata

title: MySQL with Json Data
tags: [MySQL]

---

MySQL with Json Data
===

###### tags:`MySQL`
mySql的常用指令

一、登入mySql的方式
mysql -hhostname -uroot -ppassword

Example： mysql -uroot -proot -h10.10.10.10

二、離線指令
quit
\q

三、顥示連結的狀態
status

四、建立資料庫
create database 資料庫名稱;

五、刪除資料庫
drop database 資料庫名稱;

六、show 出資料庫
show databases;

七、檢查使用中的資料庫
select database();

八、使用資料庫
use 資料庫名稱;

九、顯示資料庫內的資料表名稱
show tables;

十、顯示資料庫欄位名稱及內容
describe 資料表名稱;

十一、查看mysql的目前有那些在執行
show processlist;

十二、顯示索引表
show index 資料表名稱;

十三、匯入資料
mysql -uroot -p 資料庫 [資料檔名.sql]

# create database:
```
create database lib13;
```
# use database:
```
use lib13; 
```
# create table:
```
create table Test ( 按 Enter
id int primary key auto_increment, 按 Enter
name varchar(16) 按 Enter
); 
```

# show scheme in table:
```
show columns from Test; 
or 
describe Test;
```

# MySql 新增使用者與設定權限
Django book page:318
account :pi
password:raspberry

REF:http://pclevin.blogspot.tw/p/mysql_28.html

1.先在本地端登入MySQL服務:

```
mysql -u root -p
```
然後輸入root的密碼

2.登入成功會看到prompt:

```
mysql>
```

3.輸入權限設定命令:

```
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD';
```

> USERNAME 一般來說就是root
> IP 是開放連線的IP位址, 如果用‘%’, 會讓所有電腦皆可連線
> PASSWORD 是連線密碼

4.將權限flush, 然後離開MySQL:

```
mysql> FLUSH PRIVILEGES;
mysql> quit;
```

5.設定 Ubuntu 防火牆:

```
ufw enable
ufw allow 3306
```

6.設定 MySql的bind-address:
將bind-address取消

```
#nano /etc/mysql/my.cnf
[mysqld]
……
bind-address = 127.0.0.1 (在前面加#註解掉此行)
```
7.重新啟動MySql服務

```
service mysql restart
```

8.遠端啟動mysql client登入，以 windows 為例：
C:\AppServe\MySQL\bin\mysql –h xxx.xxx.xxx.xxx –u root -p
xxx.xxx.xxx.xxx為伺服器IP位址

# 利用Mysql來處理Json資料:  


MySQL 5.7 Introduces a JSON Data Type  
Alter table:  
ALTER TABLE people ADD COLUMN (tags json);  
JSON_ARRAY_APPEND:新增新tag在原有的json array之後  
```
UPDATE people SET tags = JSON_ARRAY_APPEND(tags, "$", "Lorem") WHERE id = 0;
```
* The three arguments to JSON_ARRAY_APPEND() are:
    * The column to append to
    * The path within the column, using the JSON Path Syntax
    * The value to append

* Json search :尋找該tags name是否位於json array裡面
    * The column to search
    * Either 'one' or 'all', depending on whether you want MySQL to just return the first match it finds, or return all matches
    * The value we're looking for
        * eg: SELECT name, tags from people WHERE JSON_SEARCH(tags, 'one', 'Lorem') IS NOT NULL;

* JSON_SET() 修改json array 資料
```
UPDATE people SET profile = JSON_SET(profile, "$.salary", 52000) WHERE id = 3;
UPDATE people SET profile = JSON_SET(profile, "$.first_aid", true) WHERE id = 3;
$代表JSON Path Syntax
Profile is column name
```
Once we've made these changes, you can inspect the state of the dataset again:
```
+----------------+---------------------------------------------------------------------------------------------------------------+
| name           | profile                                                                                                       |
+----------------+---------------------------------------------------------------------------------------------------------------+
| Howard Ortega  | {"email": "Shaw@example.com", "salary": 52000, "twitter": "@estvelit", "direct_reports": 7}                   |
| Miller Gamble  | {"salary": 52000, "join_date": "2013-05-13T10:02:22 -01:00"}                                                  |
| Harriett Leon  | {"email": "Melton@example.com", "driver": true, "salary": 63000, "twitter": "@commodoproident"}               |
| Claudia Durham | {"email": "Sykes@example.com", "salary": 52000, "first_aid": true, "vegetarian": false, "direct_reports": 12} |
| Cox Huff       | {"salary": 61000, "twitter": "@mollitconsequat"}                                                              |
+----------------+---------------------------------------------------------------------------------------------------------------+

```

* JSON_EXTRACT() for query as same as column->path:
```
SELECT name, profile->"$.direct_reports" reports, profile->"$.salary" salary FROM people WHERE profile->"$.direct_reports" >= 10;
```

# Ref:
Mysql manual:  
https://dev.mysql.com/doc/refman/5.7/en/