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