乐趣区

关于python:mysql-一次插入多行数据获取的-insertid-是第一行的-id-还是最后一行的-id

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()  # 这里必须要提交,不然所有的查问都会处于一个事务中 
退出移动版