關於MySQL5.7 這幾天的總結(json類型)

最近才知道, mysql從5.7版本開始,增加了新的欄位類型: json 

所以在centos6.5上裝了個5.7版本作為平時測試用.

#----------------------------------------------------------------------#
# 修改yum源為aliyun 
# 先備份:
  mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
# 下載配置文件 註意 centos版本
  wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repo
  yum makecache  # 生成緩存
#----------------------------------------------------------------------#


### --------------------------------------------------------------------###
# 安裝 mysql 5.7版本 不同於低版本的安裝，稍微複雜。 

yum list installed | grep mysql  # 檢測系統是否自帶安裝mysql
yum -y remove mysql-libs.x86_64  # 刪除系統自帶的mysql及其依賴
# 給CentOS添加rpm源，並且選擇較新的源
wget dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm --no-check-certificate
yum localinstall mysql-community-release-el6-5.noarch.rpm
yum repolist all | grep mysql
yum-config-manager --disable mysql55-community; 
yum-config-manager --disable mysql56-community;
yum-config-manager --enable mysql57-community-dmr; 
yum repolist enabled | grep mysql;
# 安裝mysql 伺服器
yum install mysql-community-server
service mysqld start
chkconfig --list | grep mysqld
chkconfig mysqld on
mysql_secure_installation
cat /var/log/mysqld.log | grep "password" # 找到密碼,然後登錄mysql控制台.
SET PASSWORD = PASSWORD('your password'); # 設置新密碼

### --------------------------------------------------------------------###

設計表的時候, 欄位類型直接選json  就像平常選varchar一樣.

插入數據的時候, 需要轉成JSON_OBJECT

JSON_ARRAY 用來將多個值存成數組, 
value -> '$.e1'     表示取出json型欄位名為value中的 e1 健的值. 即使取出的數字,也帶有雙引號
value ->> '$.e1'    則取出數字不帶有雙引號, 但此時仍然不是數值類型. JSON_UNQUOTE(json_extract(json,'$.attr')) 也可以去掉引號. 
value ->> '$.e1'+0  則會強制將取出的字元型數字轉為數值類型. CAST('123' AS SIGNED) 或 CONVERT('123',SIGNED)的函數也行,但是執行速度沒有直接 +0 快. (必須看起來的確是數字)

以下腳本運行在python2.7   因為python3以上不再有 MySQLdb

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb, time, datetime, json
print(datetime.datetime.now())
HOST = 'node-6'

db = MySQLdb.connect(HOST, "root", "123456", "monitor_db")
cursor = db.cursor()


def get_date(str_date):
    t = time.strptime(str_date, '%Y-%m-%d')
    y, m, d = t[0:3]
    return datetime.datetime(y, m, d)


def insert_device_data(cursor, data):
    sql = "INSERT INTO history_daily (mac, time, json) VALUES (%s, %s, %s)"
    # print sql
    cursor.executemany(sql, data)


def fetch_device_minutely_data(cursor, start, end, mac):
    try:
        sql = '''
                SELECT
                    mac,
                    CASE version %s 
                    END json 
                FROM
                    history 
                WHERE
                    mac ='%s' 
                    and time between '%s' AND '%s' 
                GROUP BY
                    mac,
                    version
            ''' % (jsonkey, mac, start, end)
        # print(sql)   # 壯觀的 case ... when

        cursor.execute(sql)
        data = cursor.fetchall()
        if data:
            for d in data:
                mac = d[0]
                json = d[1]
                device_data = []
                device_data.append((mac, start, json))
                insert_device_data(cursor, tuple(device_data))
    except MySQLdb.OperationalError, e:
        print(e)
        time.sleep(10)
        db = MySQLdb.connect(HOST, "root", "123456", "monitor_db")
        cursor = db.cursor()
        fetch_device_minutely_data(cursor, start, end, mac)


# 設備表 id , version
version_sql = 'SELECT id, version FROM device_version GROUP BY id, version'
cursor.execute(version_sql)
versions = cursor.fetchall()  # version 從2到10

jsonkey = ""
if versions:
    for version in versions:
        print (version[0])
        sensor_sql = '''
                SELECT s.sensor_key FROM device_version_sensor dvs,sensor s WHERE dvs.sensor_id = s.id AND dvs.device_version_id = '%d' 
            ''' % (version[0])  # e1  e2  e3  ...
        cursor.execute(sensor_sql)
        keys = cursor.fetchall()
        if keys:
            jsonkey += "WHEN " + str(int(version[1])) + " THEN JSON_OBJECT("
            for i, sensorKey in enumerate(keys):
                key = sensorKey[0]
                jsonkey += "'" + str(key) + "',JSON_ARRAY (round( AVG( value ->> '$." + str(
                    key) + "'+0 ), 3 ),MIN( value ->> '$." + str(
                    key) + "'+0 ), MAX( value ->> '$." + str(key) + "'+0 ))"
                if i != len(keys) - 1:
                    jsonkey += ","
            jsonkey += ")"


# mac 列表
sql_mac = 'SELECT DISTINCT mac FROM `history` where mac is not NULL';

cursor.execute(sql_mac)
mac_tuple = cursor.fetchall()  # version 從2到10

start_date = get_date((datetime.datetime.now() + datetime.timedelta(days=-6)).strftime("%Y-%m-%d"))
end_date = get_date((datetime.datetime.now()).strftime("%Y-%m-%d"))

days = (end_date - start_date).days

for i in range(days):
    start_day = start_date + datetime.timedelta(days=i)
    end_day = start_date + datetime.timedelta(days=i + 1)

    for mac in mac_tuple:
        # print (mac[0])
        fetch_device_minutely_data(cursor, start_day, end_day, mac[0])
        db.commit()

db.close()

print(datetime.datetime.now())