达梦数据库增删改查常用操作及-2723: 仅当指定列列表，且SET IDENTITY_INSERT为ON时，才能对自增列赋值问题修复

创建表

CREATE?TABLE?DICT

(

"ID" INT IDENTITY(1, 1) NOT?NULL,

"TYPE" VARCHAR(30),

"CODE" BIGINT,

"NAME" VARCHAR(300),

"VALUE" VARCHAR(200),

"DESCRIPTION" VARCHAR(255),

"OPERATOR" VARCHAR(255),

"CREATE_TIME" TIMESTAMP(0) DEFAULT?CURRENT_TIMESTAMP(),

"UPDATE_TIME" TIMESTAMP(0) DEFAULT?CURRENT_TIMESTAMP(),

PRIMARY?KEY("ID"));

COMMENT?ON?TABLE?DICT IS?'字典表';

COMMENT?ON?COLUMN?DICT."CODE" IS?'编码';

COMMENT?ON?COLUMN?DICT."DESCRIPTION" IS?'字段描述';

COMMENT?ON?COLUMN?DICT."ID" IS?'主键id';

COMMENT?ON?COLUMN?DICT."NAME" IS?'名称';

COMMENT?ON?COLUMN?DICT."TYPE" IS?'数据类型';

COMMENT?ON?COLUMN?DICT."VALUE" IS?'字符串值';

新增数据

INSERT INTO "DICT"("ID", "TYPE", "CODE", "NAME", "VALUE", "DESCRIPTION", "OPERATOR", "CREATE_TIME", "UPDATE_TIME")

VALUES(40,'Type',0,'MySQL', NULL, NULL, NULL, '2022-04-13 07:12:11', '2022-04-13 07:12:11');

报错

-2723: 仅当指定列列表，且SET IDENTITY_INSERT为ON时，才能对自增列赋值

解决方法

SET?IDENTITY_INSERT?表名?ON;

如

SET?IDENTITY_INSERT?DICT ON;

关闭语句

SET?IDENTITY_INSERT?表名?OFF;

修改语句

update?schema.dict set?type?= 'DM' where?id = 1;

查询语句

select?* from?schema.dict;

删除语句

DELETE?FROM?schema.dict;

注意事项

达梦数据库有模式(SCHEMA)的概念，增删改查有同名表的时候最好通过SCHEMA.表名方式