20230209-Oracle和MySQL数据库CTAS等操作对比

最新推荐文章于 2025-11-24 15:52:13 发布

原创最新推荐文章于 2025-11-24 15:52:13 发布 · 437 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle #mysql

Oracle 同时被 2 个专栏收录

78 篇文章

订阅专栏

MySQL

28 篇文章

订阅专栏

在这里插入图片描述

总结：

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###
欢迎关注我的公众号《IT小Chen》