mysql - HSQL 中的大写

Question

有什么方法可以在 HSQL 的创建表中为元素创建大写默认值

CREATE TABLE IF NOT EXISTS  "job" (
   "id" INTEGER IDENTITY,
   "jobId" BIGINT ,
   "jobName" VARCHAR(100)  ,
   PRIMARY KEY ("jobName")
);

现在我的需求是每次都以大写形式存储 jobName。

Answer 1

对于HSQLDB，生成列的解决方案(类似于MySQL的解决方案)是:

CREATE TABLE IF NOT EXISTS  "job" (
 "id" INTEGER IDENTITY,
 "jobId" BIGINT ,
 "jobName" VARCHAR(100)  ,
 "jobNameU" VARCHAR(100) GENERATED ALWAYS AS (UPPER("jobName")),   
 PRIMARY KEY ("jobNameU")
);

有一个更好的解决方案，使用 BEFORE INSERT 触发器，无需额外生成的列即可工作:

CREATE TABLE IF NOT EXISTS  "job" (
   "id" INTEGER IDENTITY,
   "jobId" BIGINT ,
   "jobName" VARCHAR(100)  ,
   PRIMARY KEY ("jobName")
);

CREATE TRIGGER makeUpper BEFORE INSERT ON "job" REFERENCING NEW ROW AS NEW
  FOR EACH ROW
  SET NEW."jobName" = UPPER(NEW."jobName")