MYSQL 字符隐式转换，8 VS 57 ，新人哭旧人笑？

AustinDatabases

发布于 2020-02-13 17:58:16

4940

发布于 2020-02-13 17:58:16

周六特刊的那期SQL SERVER 你不仁，别怪他不义那期本来并未期望有什么阅读量，但实际上大大的超乎想象。所以天天讲理论看的人是越来越少，讲实际遇到的问题，并解决看的人是越来越多，不管你是什么数据库。所以本期是关于MYSQL 的隐式转换，下周一是 POSTGRESQL 的隐式转换。

今天就推移到2019年的天之骄子 MYSQL 的身上，来看看 MYSQL 5.7 and MYSQL 8.017 在隐式转换上有什么需要注意的地方，有坑可以不可以躲过去。

我们先生成测试数据，可以使用下面的python 脚本，也可以自行生成测试数据，使用脚本在 MYSQL 8 和 MYSQL 5.7 中生成测试数据。

注意由于MYSQL 8 的用户密码验证的插件已经更改，PYTHON 脚本会报错，两种方法可以更改，1 改Python 脚本 2 改mysql 8 建立用户的方式（具体怎么干就不展开了）

#!/usr/bin/env python3
# coding: utf-8
from __future__ import print_function
import mysql.connector
from mysql.connector import errorcode
from datetime import date, datetime, timedelta
import re
import time
import datetime
import sys
import random


class DBFREEMEMORY:
    def __init__(self, user=None, passwd=None, host=None, db=None):
        self.user = user
        self.passwd = passwd
        self.host = host
        self.db = db

    def gen_random_string(self):

        char_list = list('1234567890' + '0123456789')
        random.shuffle(char_list)
        return ''.join(char_list)

    def mysql_connect(self):
        remotedb = {
            'host': self.host,
            'user': self.user,
            'passwd': self.passwd,
            'database': self.db,
            'charset': 'utf8',
            'connection_timeout': 30,
            'use_pure': True
        }

        try:
            connect = mysql.connector.connect(**remotedb)
            mycursor = connect.cursor(dictionary=True)

            mycursor.execute("show databases")
            database = [mycursor.fetchall()]
            # print (tables)
            database_list = re.findall('(\'.*?\')', str(database))
            database_list = [re.sub("'", '', each) for each in database_list]
            print(database_list)

            if 'test' in database_list:
                print('The database of test has existed,it has deleted it,please run the job again')
                mycursor.execute("drop database test")

            else:
                mycursor.execute("create database test")
                print('You have test database')

            DB_NAME = 'test'
            mycursor.execute("USE test".format(DB_NAME))

            TABLES = {}
            TABLES['test'] = (
                "CREATE TABLE `test1` ("
                "  `id` int(11) NOT NULL AUTO_INCREMENT,"
                "  `content` varchar(200)  NULL,"
                "  `hash` varchar(200)  NULL,"
                "  `insert_date` date  NULL,"
                "  PRIMARY KEY (`id`)"
                ") ENGINE=InnoDB")

            table_name = TABLES['test']
            # mycursor.execute(table_name)
            mycursor.execute("show tables")
            table = [mycursor.fetchall()]
            # print (tables)
            table_list = re.findall('(\'.*?\')', str(table))
            table_list = [re.sub("'", '', each) for each in table_list]
            print(table_list)

            if 'test1' in table_list:
                print('The table of test has existed,please delete it')

            else:
                try:
                    mycursor.execute(table_name)
                    #print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
                    s_time = time.time()
                    i = 0
                    while i < 150000:
                        content = self.gen_random_string()
                        sql = "INSERT INTO test1 (content, hash,insert_date) VALUES ('%s', '%d',now())" \
                              % (content, hash(content))
                        mycursor.execute(sql)
                        i += 1
                        if i % 10000 == 0:
                           connect.commit()
                           print(i)
                    connect.close()
                    print('You have test table')
                    en_time = time.time()
                    print(en_time-s_time)
                    #print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
                except Exception as e:
                    print(e)




        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                print("Something is wrong with your user name or password")
            elif err.errno == errorcode.ER_BAD_DB_ERROR:
                print("Database does not exist")
            else:
                print(err)
        finally:
            mycursor.close()
            connect.close()


if __name__ == '__main__':
    info = DBFREEMEMORY(user='admin', passwd='1234.Com', host='192.168.198.210', db='performance_schema')
    info.mysql_connect()