Oracle和MySQL資料庫CTAS等操作對比

總結：

1.透過CTAS或create table like方式備份或複製表時:
Oracle資料庫：新生成的備份表，會丟失所有索引、約束(除非空約束)。
MySQL資料庫：新生成的備份表，會自動複製表對應的索引、約束。
2.表重新命名後，Oracle、MySQL資料庫索引、約束都正常。
3.不同表，能否使用同名約束和表空間：
Oracle資料庫不可以，MySQL資料庫可以。
4.檢查性約束
Oracle資料庫：可以正常使用檢查性約束。
MySQL資料庫：建立檢查性約束正常，沒有報錯，但是無法查詢到已經建立的檢查性約束，而且檢查性約束也不起作用。
5.主鍵指定名稱
Oracle資料庫:主鍵可以指定名稱。
MySQL資料庫：主鍵建立時可以指定名稱，但是不生效，後臺只顯示名稱"PRIMARY"。

Oracle 資料庫

---建立測試表
create table t10(id int,name char(10),age int,price int,xdesc char(20));
---新增主鍵
alter table t10 add constraint pk_t10_id primary key(id);
---新增唯一約束
alter table t10 add constraint uk_t10_name unique(name);
---新增檢查約束
alter table t10 add constraint ck_t10_age check(age >=18);
---新增索引
create index i_t10_price on t10(price);

插入測試資料

insert into t10 values(1,'cjc',100,1000000,'xxx');
commit;
SQL> select * from t10;
        ID NAME              AGE      PRICE XDESC
---------- ---------- ---------- ---------- --------------------
         1 cjc               100    1000000 xxx

建表語句

SET LINE 300
SET PAGESIZE 1000
SET LONG 1000
select dbms_metadata.get_ddl('TABLE','T10','CJC') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual;
DBMS_METADATA.GET_DDL('TABLE','T10','CJC')
--------------------------------------------------------------------------------
  CREATE TABLE "CJC"."T10"
   (    "ID" NUMBER(*,0),
        "NAME" CHAR(10),
        "AGE" NUMBER(*,0),
        "PRICE" NUMBER(*,0),
        "XDESC" CHAR(20),
         CONSTRAINT "PK_T10_ID" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXT
RANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 M
AXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_
CACHE DEFAULT)
  TABLESPACE "CJCTBS"  ENABLE,
         CONSTRAINT "UK_T10_NAME" UNIQUE ("NAME")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 C
OMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINE
XTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT C
ELL_FLASH_CACHE DEFAULT)
  TABLESPACE "CJCTBS"  ENABLE,
         CONSTRAINT "CK_T10_AGE" CHECK (age >=18)
ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTR
ANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 M;

檢查約束  

set line 300
col owner for a10
col table_name for a10
col CONSTRAINT_NAME for a20
col INDEX_NAME for a15
select OWNER,TABLE_NAME,CONSTRAINT_TYPE,CONSTRAINT_NAME,INDEX_NAME,status from dba_constraints where OWNER='CJC' and table_name in ('T10');
OWNER      TABLE_NAME C CONSTRAINT_NAME      INDEX_NAME      STATUS
---------- ---------- - -------------------- --------------- --------
CJC        T10        C CK_T10_AGE                           ENABLED
CJC        T10        P PK_T10_ID            PK_T10_ID       ENABLED
CJC        T10        U UK_T10_NAME          UK_T10_NAME     ENABLED

檢查索引

set line 300
set pagesize 100
col TABLE_OWNER for a15
col TABLE_NAME for a10
col COLUMN_NAME for a10
col INDEX_NAME for a35
select TABLE_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME from dba_ind_columns where TABLE_NAME in ('T10');
TABLE_OWNER     TABLE_NAME COLUMN_NAM INDEX_NAME
--------------- ---------- ---------- -----------------------------------
CJC             T10        ID         PK_T10_ID
CJC             T10        NAME       UK_T10_NAME
CJC             T10        PRICE      I_T10_PRICE

場景一：CTAS建立表,檢查索引和約束

Create table t10_bak as select * from t10;
SQL> select * from t10_bak;
        ID NAME              AGE      PRICE XDESC
---------- ---------- ---------- ---------- --------------------
         1 cjc               100    1000000 xxx

建表語句

SET LINE 300
SET PAGESIZE 1000
SET LONG 1000
select dbms_metadata.get_ddl('TABLE','T10_BAK','CJC') from dual;
DBMS_METADATA.GET_DDL('TABLE','T10_BAK','CJC')
--------------------------------------------------------------------------------
  CREATE TABLE "CJC"."T10_BAK"
   (    "ID" NUMBER(*,0),
        "NAME" CHAR(10),
        "AGE" NUMBER(*,0),
        "PRICE" NUMBER(*,0),
        "XDESC" CHAR(20)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MIN
EXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "CJCTBS"

檢查約束，沒有約束

set line 300
col owner for a10
col table_name for a10
col CONSTRAINT_NAME for a20
col INDEX_NAME for a15
select OWNER,TABLE_NAME,CONSTRAINT_TYPE,CONSTRAINT_NAME,INDEX_NAME,status from dba_constraints where OWNER='CJC' and table_name in ('T10_BAK');
no rows selected

檢查索引，沒有索引

set line 300
set pagesize 100
col TABLE_OWNER for a15
col TABLE_NAME for a10
col COLUMN_NAME for a10
col INDEX_NAME for a35
select TABLE_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME from dba_ind_columns where TABLE_NAME in ('T10_BAK');
no rows selected

場景二：表重新命名，檢查索引和約束

alter table t10 rename to t10_1;

建表語句

SET LINE 300
SET PAGESIZE 1000
SET LONG 1000
select dbms_metadata.get_ddl('TABLE','T10_1','CJC') from dual;
DBMS_METADATA.GET_DDL('TABLE','T10_1','CJC')
--------------------------------------------------------------------------------
  CREATE TABLE "CJC"."T10_1"
   (    "ID" NUMBER(*,0),
        "NAME" CHAR(10),
        "AGE" NUMBER(*,0),
        "PRICE" NUMBER(*,0),
        "XDESC" CHAR(20),
         CONSTRAINT "PK_T10_ID" PRIMARY KEY ("ID"
)
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE S
TATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLAS
H_CACHE DEFAULT)
  TABLESPACE "CJCTBS"  ENABLE,
         CONSTRAINT "UK_T10_NAME" UNIQUE ("NAME")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
 CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "CJCTBS"  ENABLE,
         CONSTRAINT "CK_T10_AGE" CHECK (age >=18
) ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1

檢查約束，正常

set line 300
col owner for a10
col table_name for a10
col CONSTRAINT_NAME for a20
col INDEX_NAME for a15
select OWNER,TABLE_NAME,CONSTRAINT_TYPE,CONSTRAINT_NAME,INDEX_NAME,status from dba_constraints where OWNER='CJC' and table_name in ('T10_1');
OWNER      TABLE_NAME C CONSTRAINT_NAME      INDEX_NAME      STATUS
---------- ---------- - -------------------- --------------- --------
CJC        T10_1      C CK_T10_AGE                           ENABLED
CJC        T10_1      P PK_T10_ID            PK_T10_ID       ENABLED
CJC        T10_1      U UK_T10_NAME          UK_T10_NAME     ENABLED

檢查索引，正常

set line 300
set pagesize 100
col TABLE_OWNER for a15
col TABLE_NAME for a10
col COLUMN_NAME for a10
col INDEX_NAME for a35
select TABLE_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME from dba_ind_columns where TABLE_NAME in ('T10_1');
TABLE_OWNER     TABLE_NAME COLUMN_NAM INDEX_NAME
--------------- ---------- ---------- -----------------------------------
CJC             T10_1      ID         PK_T10_ID
CJC             T10_1      NAME       UK_T10_NAME
CJC             T10_1      PRICE      I_T10_PRICE

場景三：不同表建立同名約束和索引

新增表
create table t10(id int,name char(10),age int,price int,xdesc char(20));
無法新增同名約束
---新增主鍵
alter table t10 add constraint pk_t10_id primary key(id);
ORA-02264: name already used by an existing constraint
---新增唯一約束
alter table t10 add constraint uk_t10_name unique(name);
ORA-02264: name already used by an existing constraint
---新增檢查約束
alter table t10 add constraint ck_t10_age check(age >=18);
ORA-02264: name already used by an existing constraint
無法新增同名索引
---新增索引
create index i_t10_price on t10(price);
ORA-00955: name is already used by an existing object

場景四：檢查性約束

SQL> insert into t10_1 values(2,'chen',15,2000000,'hhh');
insert into t10_1 values(2,'chen',15,2000000,'hhh')
*
ERROR at line 1:
ORA-02290: check constraint (CJC.CK_T10_AGE) violated

MySQL資料庫測試

---建立測試表
create table t10(id int,name char(10),age int,price int,xdesc char(20));
---新增主鍵
alter table t10 add constraint pk_t10_id primary key(id);
---新增唯一約束
alter table t10 add constraint uk_t10_name unique(name);
---新增檢查約束
alter table t10 add constraint ck_t10_age check(age >=18);
---新增索引
alter table t10 add index i_t10_price (price);

檢視建表語句

[cjctest]> show create table t10\G;
*************************** 1. row ***************************
       Table: t10
Create Table: CREATE TABLE `t10` (
  `id` int(11) NOT NULL,
  `name` char(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `xdesc` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_t10_name` (`name`),
  KEY `i_t10_price` (`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

插入測試資料

insert into t10 values(1,'cjc',100,1000000,'xxx');
root@localhost:mysql.sock 09:39 [cjctest]> select * from t10;
+----+------+------+---------+-------+
| id | name | age  | price   | xdesc |
+----+------+------+---------+-------+
|  1 | cjc  |  100 | 1000000 | xxx   |
+----+------+------+---------+-------+
1 row in set (0.00 sec)

場景一：CTAS建立表,檢查索引和約束

create table t10_bak as select * from t10;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
啟用了GTID，需要先複製表結構，在插入資料
create table t10_bak like t10;
insert into t10_bak select * from t10;

檢視錶結構

show create table t10_bak\G;
*************************** 1. row ***************************
       Table: t10_bak
Create Table: CREATE TABLE `t10_bak` (
  `id` int(11) NOT NULL,
  `name` char(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `xdesc` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_t10_name` (`name`),
  KEY `i_t10_price` (`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
show create table t10\G;
*************************** 1. row ***************************
       Table: t10
Create Table: CREATE TABLE `t10` (
  `id` int(11) NOT NULL,
  `name` char(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `xdesc` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_t10_name` (`name`),
  KEY `i_t10_price` (`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

檢視約束

[cjctest]> select CONSTRAINT_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from information_schema.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA='cjctest' and table_name like 't10%' order by 2,3;
+-------------------+------------+-----------------+-----------------+
| CONSTRAINT_SCHEMA | TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE |
+-------------------+------------+-----------------+-----------------+
| cjctest           | t10        | PRIMARY         | PRIMARY KEY     |
| cjctest           | t10        | uk_t10_name     | UNIQUE          |
| cjctest           | t10_bak    | PRIMARY         | PRIMARY KEY     |
| cjctest           | t10_bak    | uk_t10_name     | UNIQUE          |
+-------------------+------------+-----------------+-----------------+
4 rows in set (0.00 sec)

場景二：表重新命名，檢查索引和約束

alter table t10 rename t10_1;

查詢表結構

root@localhost:mysql.sock 10:59 [cjctest]> show create table t10_1\G;
*************************** 1. row ***************************
       Table: t10_1
Create Table: CREATE TABLE `t10_1` (
  `id` int(11) NOT NULL,
  `name` char(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `xdesc` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_t10_name` (`name`),
  KEY `i_t10_price` (`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost:mysql.sock 10:59 [cjctest]> select * from t10_1;
+----+------+------+---------+-------+
| id | name | age  | price   | xdesc |
+----+------+------+---------+-------+
|  1 | cjc  |  100 | 1000000 | xxx   |
+----+------+------+---------+-------+
1 row in set (0.00 sec)

檢視約束

[cjctest]> select CONSTRAINT_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from information_schema.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA='cjctest' and table_name = 't10_1' order by 2,3;
+-------------------+------------+-----------------+-----------------+
| CONSTRAINT_SCHEMA | TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE |
+-------------------+------------+-----------------+-----------------+
| cjctest           | t10_1      | PRIMARY         | PRIMARY KEY     |
| cjctest           | t10_1      | uk_t10_name     | UNIQUE          |
+-------------------+------------+-----------------+-----------------+
2 rows in set (0.00 sec)

場景三：不同表建立同名約束和索引

MySQL 建立測試表

create table t10(id int,name char(10),age int,price int,xdesc char(20));

---新增主鍵
alter table t10 add constraint pk_t10_id primary key(id);
---新增唯一約束
alter table t10 add constraint uk_t10_name unique(name);
---新增檢查約束
alter table t10 add constraint ck_t10_age check(age >=18);
---新增索引
alter table t10 add index i_t10_price (price);

檢查約束

root@localhost:mysql.sock 11:02 [cjctest]> select CONSTRAINT_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from information_schema.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA='cjctest' and table_name like 't10%' order by 2,3;
+-------------------+------------+-----------------+-----------------+
| CONSTRAINT_SCHEMA | TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE |
+-------------------+------------+-----------------+-----------------+
| cjctest           | t10        | PRIMARY         | PRIMARY KEY     |
| cjctest           | t10        | uk_t10_name     | UNIQUE          |
| cjctest           | t10_1      | PRIMARY         | PRIMARY KEY     |
| cjctest           | t10_1      | uk_t10_name     | UNIQUE          |
| cjctest           | t10_bak    | PRIMARY         | PRIMARY KEY     |
| cjctest           | t10_bak    | uk_t10_name     | UNIQUE          |
+-------------------+------------+-----------------+-----------------+
6 rows in set (0.00 sec)

場景四：檢查性約束

root@localhost:mysql.sock 11:09 [cjctest]> insert into t10 values(3,'ccc',1,3000000,'aaa');
Query OK, 1 row affected (0.00 sec)

###chenjuchao 20230209###