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

周六特刊的那期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-8from__future__ importprint_function

importmysql.connector

frommysql.connector importerrorcode

fromdatetime importdate, datetime, timedelta

importre

importtime

importdatetime

importsys

importrandom

classDBFREEMEMORY:

def__init__(self, user=None, passwd=None, host=None, db=None):

self.user = user

self.passwd = passwd

self.host = host

self.db = db

defgen_random_string(self):

char_list = list('1234567890'+ '0123456789')

random.shuffle(char_list)

return''.join(char_list)

defmysql_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) foreach indatabase_list]

print(database_list)

if'test'indatabase_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) foreach intable_list]

print(table_list)

if'test1'intable_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 = 0whilei < 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 += 1ifi % 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()))exceptExceptionase:

print(e)

exceptmysql.connector.Error aserr:

iferr.errno == errorcode.ER_ACCESS_DENIED_ERROR:

print("Something is wrong with your user name or password")

eliferr.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()

在MYSQL  8.017 和 MYSQL 5.7.25 中生成数据后，

首先我们看都是可以走索引的，由于MYSQL 的CAST 转换中，只支持CHAR 的转换，而我们的表的字段类型(对应的)是varchar， 所以隐式转换不影响MYSQL 8  ， MYSQL 57，并且可以走索引。

但实际上，我们稍微的一转换，就可以让某些版本的数据库的隐式转换重新重现“不行” 的状态。从下面两张图可以看出，MYSQL 8 在执行同样的语句是，已经是不能进行正常的隐式转换了，在有索引的情况下，不能正常工作，而MYSQL 57 在更换了表的 DEFAULT CHARSET后。

首先从上面的测试得出两个结果mysql的表的建立的时候，最好使用 utf8mb4 的类型

那下面有同学问了，到底为什么 ，到底为什么， 别说还真知道，下面看完两个图你也能明白

1  MYSQL 5.7

2 MYSQL 8

在MYSQL 8 中 cast 默认转换为UTF8MB4  而 MYSQL 57 默认转换为 UTF8

所以和 SQL SERVER 类似的问题， 在字符的转换过程中，要注意你的表的 DEFAULT CHARSET ，并统一DEFAULT CHARSET， 否则让你措手不及的地方还多着呢。

如果有进行数据库交流的同学，可以加这个群，一起学习，群里有一些共享文件，如果需要可以自行下载

共享资源，Share database knowledges and learn it