共计 1399 个字符,预计需要花费 4 分钟才能阅读完成。
mysql server 版本:8.0.31
Q: 对于 mysql 协定,insert 的返回值外面蕴含什么信息?是否蕴含 insert_id?
A: 通过 wireshark 抓包,后果如下:
执行 sql:
INSERT IGNORE INTO `user` (`name`, `age`) VALUES ('1', 25), ('2', 25), ('2', 25);
mysql server 返回的内容如下:
Q:mysql 一次插入多行数据,获取的 insert_id 是第一行的 id 还是最初一行的 id?
A:从上图可知,是第一个!mysql 的开发人员真是一群笨蛋!
测试代码如下:
from loguru import logger
from peewee import *
import settings
from playhouse.shortcuts import ReconnectMixin
from pymysql.cursors import Cursor
host = settings.MYSQL_CONFIG.host
port = settings.MYSQL_CONFIG.port
username = settings.MYSQL_CONFIG.username
password = settings.MYSQL_CONFIG.password
database_name = settings.MYSQL_CONFIG.database_name
class ReconnectMySQLDatabase(ReconnectMixin, MySQLDatabase):
pass
db = ReconnectMySQLDatabase(
database=database_name,
host=host,
port=port,
user=username,
password=password,
charset='utf8mb4'
)
class User(Model):
name = CharField(unique=True)
age = IntegerField()
created_at = DateTimeField(
null=False,
constraints=[SQL('DEFAULT CURRENT_TIMESTAMP')],
help_text='应用数据库工夫'
)
updated_at = DateTimeField(
null=False,
constraints=[SQL('DEFAULT CURRENT_TIMESTAMP'),
SQL('ON UPDATE CURRENT_TIMESTAMP'),
]
)
class Meta:
database = db
table_name = 'user'
model_set = [User]
db.drop_tables(model_set)
db.create_tables(model_set)
with db.cursor() as cursor:
cursor: Cursor
sql = """INSERT IGNORE INTO `user` (`name`, `age`) VALUES ('1', 25), ('2', 25), ('2', 25);""".strip()
print('affected_rows:', cursor.execute(sql))
print('lastrowid:',cursor.lastrowid)
cursor.connection.commit() # 这里必须要提交,不然所有的查问都会处于一个事务中
正文完