MySQL 8执行UPDATE时自增主键字段报错：无默认值

问题场景 #

将旧MySQL数据库迁移至MySQL 8服务器后，执行仅更新artikel表部分字段的UPDATE语句时触发错误：

UPDATE artikel
SET personalnummer    = 'p325111',
    empfangendes_haus = '612',
    status            = '10',
    freigegeben       = 'nein',
    aenderung_durch   = 'intranet',
    kommentar         = 'serdfghfdf',
    seriennummer      = 'y5e4rzthfrzu',
    lagerstellplatz   = 'jhgfds',
    zustand           = 0,
    zeitpunkt         = '2023-07-24 18:41:45'
WHERE barcode = '59655420705527500276'

错误提示：

[HY000][1364] Field 'inkrement' doesn't have a default value

其中inkrement是artikel表的bigint类型自增主键，无默认值符合设计预期。

artikel表结构及触发器定义如下：

create table if not exists mimo2.artikel
(
    barcode                           varchar(50)                                                  default ''                not null,
    inkrement                         bigint auto_increment primary key,
    lieferscheinnr                    int unsigned                                                 default '0'               not null,
    rechnungsnr                       varchar(16)                                                  default ''                not null,
    lieferantennr                     int(6) unsigned zerofill                                     default 0                 not null,
    artikelnr                         varchar(8)                                                   default ''                not null,
    laufende_nr                       int unsigned                                                 default '0'               not null,
    pruefziffer                       tinyint unsigned                                             default '0'               not null,
    ausgehendes_haus                  int unsigned                                                 default '0'               not null,
    empfangendes_haus                 int unsigned                                                 default '0'               not null,
    datum_aenderung_empfangendes_haus datetime                                                                               null,
    lagerort                          enum ('nebenflaeche', 'verkauf', 'undefiniert')              default 'undefiniert'     not null,
    freigegeben                       enum ('ja', 'nein')                                          default 'nein'            not null,
    status                            int unsigned                                                 default '3'               not null,
    datum_statusaenderung             datetime                                                                               null,
    preis                             double(20, 2)                                                default 0.00              not null,
    waehrung                          varchar(255)                                                 default ''                not null,
    personalnummer                    varchar(7)                                                   default ''                not null,
    zeitpunkt                         timestamp                                                    default CURRENT_TIMESTAMP not null,
    kommentar                         longtext                                                                               not null,
    bildpfad                          varchar(255)                                                                           null,
    aenderung_durch                   enum ('lieferant', 'dienstleister', 'intranet', 'unbekannt') default 'unbekannt'       not null,
    datum_anlage_initial              datetime                                                                               not null comment 'Anlage des ersten Datnesatzes zu dem Barcode',
    duplicate                         enum ('none', 'mark', 'dublicated', 'duplicated')            default 'none'            not null,
    zustand                           tinyint                                                      default 0                 not null,
    seriennummer                      text                                                                                   not null,
    lagerstellplatz                   text                                                                                   not null,
    constraint Secondary_Key
        unique (barcode),
    constraint artikel_ibfk_1
        foreign key (status) references mimo2.status (nr)
            on update cascade on delete cascade,
    constraint artikel_ibfk_2
        foreign key (zustand) references mimo2.zustand (zustand)
            on update cascade on delete cascade
)
    collate = utf8_unicode_ci;

create index artikelnr
    on mimo2.artikel (artikelnr);

create index ausgehendes_haus
    on mimo2.artikel (ausgehendes_haus);

create index datum_aenderung_empfangendes_haus
    on mimo2.artikel (datum_aenderung_empfangendes_haus);

create index empfangendes_haus
    on mimo2.artikel (empfangendes_haus);

create index freigegeben
    on mimo2.artikel (freigegeben);

create index lagerort
    on mimo2.artikel (lagerort);

create index laufende_nr
    on mimo2.artikel (laufende_nr);

create index lieferantennr
    on mimo2.artikel (lieferantennr);

create index personalnummer
    on mimo2.artikel (personalnummer);

create index status
    on mimo2.artikel (status);

create index zeitpunkt
    on mimo2.artikel (zeitpunkt);

create index zustand
    on mimo2.artikel (zustand);

create definer = `my-db-user`@`` trigger mimo2.artikel_history_before_delete
    before delete
    on mimo2.artikel
    for each row
    INSERT INTO mimo2.artikel_historie
    SET barcode              = OLD.barcode,
        lieferscheinnr       = OLD.lieferscheinnr,
        rechnungsnr          = OLD.rechnungsnr,
        lieferantennr        = OLD.lieferantennr,
        artikelnr            = OLD.artikelnr,
        laufende_nr          = OLD.laufende_nr,
        pruefziffer          = OLD.pruefziffer,
        ausgehendes_haus     = OLD.ausgehendes_haus,
        empfangendes_haus    = OLD.empfangendes_haus,
        lagerort             = OLD.lagerort,
        freigegeben          = OLD.freigegeben,
        status               = OLD.status,
        preis                = OLD.preis,
        waehrung             = OLD.waehrung,
        personalnummer       = OLD.personalnummer,
        zeitpunkt            = OLD.zeitpunkt,
        kommentar            = OLD.kommentar,
        bildpfad             = OLD.bildpfad,
        aenderung_durch      = OLD.aenderung_durch,
        datum_anlage_initial = OLD.datum_anlage_initial,
        dublicate            = OLD.duplicate,
        zustand              = OLD.zustand,
        historie_datum       = NOW();

create definer = `my-db-user`@`` trigger mimo2.artikel_history_before_update
    before update
    on mimo2.artikel
    for each row
    INSERT INTO mimo2.artikel_historie
    SET barcode              = OLD.barcode,
        lieferscheinnr       = OLD.lieferscheinnr,
        rechnungsnr          = OLD.rechnungsnr,
        lieferantennr        = OLD.lieferantennr,
        artikelnr            = OLD.artikelnr,
        laufende_nr          = OLD.laufende_nr,
        pruefziffer          = OLD.pruefziffer,
        ausgehendes_haus     = OLD.ausgehendes_haus,
        empfangendes_haus    = OLD.empfangendes_haus,
        lagerort             = OLD.lagerort,
        freigegeben          = OLD.freigegeben,
        status               = OLD.status,
        preis                = OLD.preis,
        waehrung             = OLD.waehrung,
        personalnummer       = OLD.personalnummer,
        zeitpunkt            = OLD.zeitpunkt,
        kommentar            = OLD.kommentar,
        bildpfad             = OLD.bildpfad,
        aenderung_durch      = OLD.aenderung_durch,
        datum_anlage_initial = OLD.datum_anlage_initial,
        dublicate            = OLD.duplicate,
        zustand              = OLD.zustand,
        historie_datum       = NOW();

原因分析 #

错误并非来自UPDATE语句本身，而是由**artikel_history_before_update触发器**导致：
触发器在更新artikel表前，会向artikel_historie表插入旧数据，但触发器的INSERT语句中未包含inkrement字段。而artikel_historie表的inkrement字段（与主表结构一致，为非空且无默认值的字段）在MySQL 8默认的严格SQL模式下，未赋值就插入会触发1364错误。旧版本MySQL可能会自动填充默认值（如0），但MySQL 8的严格模式禁用了这种行为。

解决方案 #

方案1：修改触发器，补充inkrement字段的插入值 #

在两个触发器的INSERT语句中，添加inkrement = OLD.inkrement，直接将主表旧数据的主键值插入到历史表：
修改后的更新触发器示例：

create definer = `my-db-user`@`` trigger mimo2.artikel_history_before_update
    before update
    on mimo2.artikel
    for each row
    INSERT INTO mimo2.artikel_historie
    SET barcode              = OLD.barcode,
        inkrement            = OLD.inkrement, -- 新增该行，插入主表旧主键值
        lieferscheinnr       = OLD.lieferscheinnr,
        rechnungsnr          = OLD.rechnungsnr,
        lieferantennr        = OLD.lieferantennr,
        artikelnr            = OLD.artikelnr,
        laufende_nr          = OLD.laufende_nr,
        pruefziffer          = OLD.pruefziffer,
        ausgehendes_haus     = OLD.ausgehendes_haus,
        empfangendes_haus    = OLD.empfangendes_haus,
        lagerort             = OLD.lagerort,
        freigegeben          = OLD.freigegeben,
        status               = OLD.status,
        preis                = OLD.preis,
        waehrung             = OLD.waehrung,
        personalnummer       = OLD.personalnummer,
        zeitpunkt            = OLD.zeitpunkt,
        kommentar            = OLD.kommentar,
        bildpfad             = OLD.bildpfad,
        aenderung_durch      = OLD.aenderung_durch,
        datum_anlage_initial = OLD.datum_anlage_initial,
        dublicate            = OLD.duplicate,
        zustand              = OLD.zustand,
        historie_datum       = NOW();

删除触发器做同样修改，添加inkrement = OLD.inkrement即可。

方案2：修改artikel_historie表结构 #

如果历史表的inkrement不需要与主表主键关联，可以：

1. 将其设置为自增主键，插入时无需手动赋值：

ALTER TABLE mimo2.artikel_historie MODIFY COLUMN inkrement bigint auto_increment primary key;

2. 或给该字段设置默认值：

ALTER TABLE mimo2.artikel_historie MODIFY COLUMN inkrement bigint DEFAULT 0 NOT NULL;

内容的提问来源于stack exchange，提问作者Calamity Jane