database transplant

This week, I have a task to transplant the Oracle9i's infrastructure to MySQL in order to decrease our corp's cost. I haven't finished it so far, because I have met lots of problems.

MySQL's syntax is different from Oracle9i's. It really troubles me. Now I list some differences I have met.

1.datatype;

2.constraint;

3.index;

4.trigger;

5.sequence;

6.other;

Before that, I haven't really use mysql's command line to create a trigger and a index, but now, I learned to write some codes. It does work, I've make a big step, I know only that could I finish my task confidently. Below that I plan to examplify some cases which I will solve them later.

1.datatype:

CREATE TABLE "table_name"(      "number" NUMBER(19, 0) NOT NULL,      "varchar" VARCHAR2(255) NOT NULL,      "timestamp" TIMESTAMP(6) NULL );

2.constraint:

ALTER TABLE "table_name"  ADD CONSTRAINT "constraint_name" PRIMARY KEY ("column_name"); ALTER TABLE "table_name"  ADD CONSTRAINT "constraint_name" CHECK ("column_name" IS NOT NULL);ALTER TABLE "table_name"  ADD CONSTRAINT "constraint_name" FOREIGN KEY ("column_name")  REFERENCES "table_name"("column_name");

3.index

CREATE INDEX "index_name"     ON "table_name"  ("column_name") ;

4.trigger:

CREATE OR REPLACE TRIGGER "trigger_name" AFTERINSERTOR UPDATE OF "column_name" ON "table_name" FOR EACH ROWBEGIN insert into "table_name"("column_name") values(sq_trade_audit.nextval, :new."column_name",);END;

5.sequence:

DROP SEQUENCE sequence_name;CREATE SEQUENCE sequence_name;

6.other:

PCTFREE 10PCTUSED 60INITRANS 10MAXTRANS 255CACHESTORAGE(INITIAL 51200k NEXT 25600K PCTINCREASE 0);