达梦数据库操作以及报错修改

执行失败(语句1)

-6105:: 数据类型不匹配

第12 行附近出现错误

插入sql语句
INSERT INTO "by_ioc_rbac"."user_info" ("user_account", "user_name", "birthday", "password", "gender", "mobile", "email", "supplier_id", "user_type", "create_time", "duties", "dispatch_mode", "status", "id_copy", "uuid", "online_status", "update_time", "authentication_status", "id_card"
) 
VALUES ('user123', 'John Doe', '1990-01-01', 'securepassword', 1, '1234567890', '[e-mail]', 1001, 2, CURRENT_TIMESTAMP, 'Manager', 1, 1, NULL, 'uuid-12345', 0, CURRENT_TIMESTAMP, 0, '123456789012345678'
);
数据库ddl
CREATE TABLE "by_ioc_rbac"."user_info" ("id" BIGINT NOT NULL AUTO_INCREMENT,"user_account" VARCHAR(90) NULL,"user_name" VARCHAR(90) NULL,"birthday" VARCHAR(90) NULL,"password" VARCHAR(248) NULL,"gender" SMALLINT NULL,"mobile" VARCHAR(32) NULL,"email" VARCHAR(64) NULL,"supplier_id" BIGINT NULL,"user_type" SMALLINT NULL,"create_time" TIMESTAMP NULL,"duties" VARCHAR(2048) NULL,"dispatch_mode" SMALLINT NULL,"status" SMALLINT NULL,"id_copy" BIGINT NULL,"uuid" VARCHAR(90) NULL,"online_status" TINYINT NULL,"update_time" TIMESTAMP NULL,"authentication_status" SMALLINT DEFAULT 0 NULL,"id_card" VARCHAR(128) NULL,CONSTRAINT CONS134219136 PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX INDEX33556236 ON "by_ioc_rbac"."user_info" ("id");
CREATE INDEX "idx_user_info_email" ON "by_ioc_rbac"."user_info" ("email");
CREATE INDEX "idx_user_info_mobile" ON "by_ioc_rbac"."user_info" ("mobile");
CREATE INDEX "idx_user_info_status" ON "by_ioc_rbac"."user_info" ("status");
CREATE INDEX "idx_user_info_supplier_id" ON "by_ioc_rbac"."user_info" ("supplier_id");
CREATE INDEX "idx_user_info_supplier_id_status" ON "by_ioc_rbac"."user_info" ("supplier_id","status");
CREATE INDEX "idx_user_info_user_account" ON "by_ioc_rbac"."user_info" ("user_account");
CREATE INDEX "idx_user_info_user_account_user_name_mobile_email" ON "by_ioc_rbac"."user_info" ("user_account","user_name","mobile","email");
CREATE INDEX "idx_user_info_user_name" ON "by_ioc_rbac"."user_info" ("user_name");
CREATE INDEX "idx_user_info_user_type" ON "by_ioc_rbac"."user_info" ("user_type");

异常

dm.jdbc.driver.DMException: 无效的数据类型at dm.jdbc.driver.DBError.throwException(SourceFile:738)at dm.jdbc.a.a.y.r(SourceFile:623)at dm.jdbc.a.a.f.r(SourceFile:138)at dm.jdbc.a.a.y.z(SourceFile:555)at dm.jdbc.a.a.y.L(SourceFile:536)at dm.jdbc.a.a.a(SourceFile:269)at dm.jdbc.a.a.a(SourceFile:803)at dm.jdbc.driver.DmdbStatement.executeInner(SourceFile:771)at dm.jdbc.driver.DmdbStatement.do_execute(SourceFile:196)at dm.jdbc.driver.DmdbStatement.do_execute(SourceFile:188)at dm.jdbc.driver.DmdbStatement.execute(SourceFile:1341)at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)at org.jkiss.dbeaver.model.exec.DBExecUtils.executePersistAction(DBExecUtils.java:361)at org.jkiss.dbeaver.model.impl.edit.AbstractObjectManager.executePersistAction(AbstractObjectManager.java:40)at org.jkiss.dbeaver.model.impl.edit.AbstractCommandContext.executeCommands(AbstractCommandContext.java:198)at org.jkiss.dbeaver.model.impl.edit.AbstractCommandContext.saveChanges(AbstractCommandContext.java:125)at org.jkiss.dbeaver.ui.editors.entity.EntityEditor.lambda$1(EntityEditor.java:392)at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:192)at org.jkiss.dbeaver.ui.editors.entity.EntityEditor.saveCommandContext(EntityEditor.java:390)at org.jkiss.dbeaver.ui.editors.entity.EntityEditor$SaveJob.run(EntityEditor.java:1261)at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:117)at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

排查思路：

1、首先想到的是插入的字段类型与数据库定义的字段类型不匹配导致的，经过检查insert语句的只字段类型于数据库定义一致。所以不是此原因。

2、因为是数据库存在数据记录，将数据复制为insert语句执行插入也失败。

3、执行update语句也报此异常；

解决方案：

1、复制已有表ddl语句，只保留create语句。建立一个测试表，往测试表插入数据正常。

2、分析原有表的ddl语句发现存在约束"create_time" >= 0

3、数据库最原始的时间字段是DECIMAL类型，存入数据是时间戳。设置了一个大0的约束。之后时间字段为DATETIME类型之后，时间字段的值不能和0比较。所以报错。

总结：数据库数据类型不匹配，分析是否存在约束数据，如果存在则查插入或者更新的数据是否不符合数据表已有的约束信息

数据库操作

1、新增字段

ALTER TABLE "数据库名"."表名" ADD 字段名 字段类型;
-- 举例:
ALTER TABLE "SZZY"."feedback_info" ADD mark VARCHAR2(256);

2、修改字段

alter table test_table modify (test_column varchar(19) not null);

3、删除表中的字段

ALTER TABLE "数据库名"."表名" DROP COLUMN 字段名;
-- 举例：
ALTER TABLE "SZZY"."feedback_info" DROP COLUMN case_id;

4、给字段添加备注

comment ON COLUMN "数据库名"."表名"."字段名" IS '备注名';
-- 举例：
comment ON COLUMN "SZZY"."feedback_info"."mark" IS '标记';

5、给指定字段添加默认值

ALTER TABLE "数据库名"."表名" ALTER COLUMN 字段名 SET DEFAULT 默认值;
-- 举例：
ALTER TABLE "SZZY"."feedback_info" ALTER COLUMN status SET DEFAULT 0;

6、删除指定字段默认值

ALTER TABLE "数据库名"."表名" ALTER COLUMN 字段名 DROP DEFAULT;
-- 举例：
ALTER TABLE "SZZY"."feedback_info" ALTER COLUMN mark DROP DEFAULT;

7、增删改查

-- 查找
SELECT 字段名 FROM "表名" WHERE 条件;
SELECT "id", "case_code" FROM "case_info" WHERE "case_code" LIKE '%民终33%';
===========================================================================
-- 修改
UPDATE 数据库名."表名" SET "字段名" = '修改值' WHERE 条件;
UPDATE SZZY."feedback_info" SET "status" = '1' WHERE id = 1745737045470896128;
===========================================================================
-- 增加
INSERT INTO "数据库名"."表名" (字段列表) VALUES (字段值列表) ;
INSERT INTO "SZZY"."feedback_info" ("func_code","fdbk_person") VALUES ('04', '管理员') ;
===========================================================================
-- 删除
DELETE FROM "数据库名"."表名" WHERE 条件;
DELETE FROM "SZZY"."feedback_info" WHERE "fdbk_date" < '2024-01-11 16:06:40';

8、修改表名

ALTER TABLE 旧表名 RENAME TO 新表名;
ALTER TABLE yuangong RENAME TO employee;

9、增加表注释

comment ON TABLE 表名 is ‘注释’

10、达梦数据库 修改字段名

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;