Oracle兼容-语法-CREATE TABLE OF TYPE

1. 语法

1. SET sql_mode = ORACLE;

3. CREATE TABLE [IF NOT EXISTS] table_name OF type_name [table options]

需要先切换到 ORACLE 模式下才能支持本语法。

2. 定义和用法

GreatSQL支持用户通过 CREATE TABLE OF TYPE 创建自定义数据类型。

3. Oracle兼容说明

GreatSQL中的CREATE TABLE OF TYPE与Oracle兼容情况说明如下：

  1. 不支持 CREATE TEMPORARY TABLE OF TYPE 用法。
  2. 建表时表定义参数 table options 中不支持设置 SECONDARY_ENGINE, AVG_ROW_LENGTH, ROW_FORMAT, SECONDARY_ENGINE_ATTRIBUTE, UNION, AUTO_INCREMENT, PARTITION BY 等几个属性。
  3. 不允许修改表结构，但支持 ALTER TABLE ... RENAME, ALTER TABLE ... COMMENT, CREATE INDEX, DROP INDEX, ALTER INDEX 等几种操作。
  4. 若有其他表存在依赖/继承关系，在删除这些表之前不允许 DROP TABLE TYPE。
  5. 支持跨库引用创建新表，但必须和引用的 TABLE TYPE 在同一个Schema里。
  6. 支持 INSERT ... VALUES 和 INSERT ... SELECT 两种方式写入数据。
  7. 不允许插入 TYPE = NULL 值，但可以插入 TYPE(NULL,NULL) 值。
  8. 更新数据时，只能对列进行一对一更新，不能使用类似 UPDATE TABLE SET(col1, col2) = (udt_type(val1, val2)) 方式进行更新。

4. 示例

    1. 示例1：建表及写数据
1. greatsql> SET sql_mode = ORACLE;

3. -- 1. 建表
4. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 VARCHAR(20));

6. greatsql> CREATE TABLE tf_t1 OF udt1;

8. -- 2. 写数据 INSERT ... VALUES
9. greatsql> INSERT INTO tf_t1 VALUES(1, 'c1_row1');

11. -- 3. 写数据 INSERT ... SELECT
12. greatsql> INSERT INTO tf_t1 SELECT udt1(2, 'c1_row2');

14. greatsql> SELECT * FROM tf_t1;
15. +------+---------+
16. | id   | c1      |
17. +------+---------+
18. |    1 | c1_row1 |
19. |    2 | c1_row2 |
20. +------+---------+

22. greatsql> CREATE TABLE tf_t2 (id INT, c1 udt1);

24. greatsql> INSERT INTO tf_t2 VALUES(10, udt1(10, 'tf_t2_row10')),
25. (20, udt1(20,'tf_t2_row20')),
26. (30, udt1(30,'tf_t2_row30')),
27. (40, udt1(40,'tf_t2_row40'));

29. greatsql> INSERT INTO tf_t1 SELECT c1 FROM tf_t2;

31. greatsql> SET udt_format_result = DBA;

33. greatsql> SELECT * FROM tf_t2;
34. +------+------------------------+
35. | id   | c1                     |
36. +------+------------------------+
37. |   10 | id:10 | c1:tf_t2_row10 |
38. |   20 | id:20 | c1:tf_t2_row20 |
39. |   30 | id:30 | c1:tf_t2_row30 |
40. |   40 | id:40 | c1:tf_t2_row40 |
41. +------+------------------------+

43. greatsql> SELECT * FROM tf_t1;
44. +------+-------------+
45. | id   | c1          |
46. +------+-------------+
47. |    1 | c1_row1     |
48. |    2 | c1_row2     |
49. |   10 | tf_t2_row10 |
50. |   20 | tf_t2_row20 |
51. |   30 | tf_t2_row30 |
52. |   40 | tf_t2_row40 |
53. +------+-------------+

55. -- 4. 查看表结构
56. greatsql> SHOW CREATE TABLE tf_t1\G
57. *************************** 1. row ***************************
58. Table: tf_t1
59. Create Table: CREATE TABLE "tf_t1" (
60. "id" int DEFAULT NULL,
61. "c1" varchar(20) DEFAULT NULL
62. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

64. greatsql> SHOW CREATE TABLE tf_t2\G
65. *************************** 1. row ***************************
66. Table: tf_t2
67. Create Table: CREATE TABLE "tf_t2" (
68. "id" int DEFAULT NULL,
69. "c1" udt1 DEFAULT NULL,
70. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    1. 示例2：修改表

本示例基于示例1中的表和数据，不再重复执行。

1. -- 不能修改表结构
2. greatsql> ALTER TABLE tf_t1 ADD i INT;
3. ERROR 7552 (HY000): cannot alter column from an object type table

5. greatsql> ALTER TABLE tf_t1 DROP COLUMN id;
6. ERROR 7552 (HY000): cannot alter column from an object type table

8. greatsql> ALTER TABLE tf_t1 MODIFY c1 CHAR(20);
9. ERROR 7552 (HY000): cannot alter column from an object type table

11. -- 可以增加COMMENT
12. greatsql> ALTER TABLE tf_t1 COMMENT ='UDT TABLE';

14. -- 还可以创建索引
15. greatsql> CREATE INDEX idx_c1 ON tf_t1(c1);

17. greatsql> SHOW CREATE TABLE tf_t1\G
18. *************************** 1. row ***************************
19. Table: tf_t1
20. Create Table: CREATE TABLE "tf_t1" (
21. "id" int DEFAULT NULL,
22. "c1" varchar(20) DEFAULT NULL,
23. KEY "idx_c1" ("c1")
24. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='UDT TABLE'

26. -- 不支持对UDT列创建索引，只能对普通列创建索引
27. greatsql> CREATE INDEX idx_c1 ON tf_t2(id, c1);
28. ERROR 7549 (42000): Udf type column 'c1' can't be used in key specification with the used table type

30. greatsql> CREATE INDEX idx_c1 ON tf_t2(id);

32. greatsql> SHOW CREATE TABLE tf_t2\G
33. *************************** 1. row ***************************
34. Table: tf_t2
35. Create Table: CREATE TABLE "tf_t2" (
36. "id" int DEFAULT NULL,
37. "c1" udt1 DEFAULT NULL,
38. KEY "idx_c1" ("id")
39. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

5. TABLE TYPE数据字典

1. -- 1. 查询 information_schema.TABLES 查看所有 TABLE

3. -- 查询结果中，CREATE_OPTIONS包含udt_name关键字的表
4. greatsql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS, TABLE_COMMENT
5. FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'greatsql';
6. +--------------+------------+-----------------+---------------+
7. | TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS  | TABLE_COMMENT |
8. +--------------+------------+-----------------+---------------+
9. | greatsql     | tf_t1      | udt_name="udt1" | UDT TABLE     |
10. | greatsql     | tf_t2      |                 |               |
11. +--------------+------------+-----------------+---------------+

13. -- 2. 查询 information_schema.COLUMNS 查看表中所有列
14. greatsql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, EXTRA
15. FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'greatsql';
16. +--------------+------------+-------------+------------------+-----------+-----------------+
17. | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | EXTRA           |
18. +--------------+------------+-------------+------------------+-----------+-----------------+
19. | greatsql     | tf_t1      | id          |                1 | int       |                 |
20. | greatsql     | tf_t1      | c1          |                2 | varchar   |                 |
21. | greatsql     | tf_t2      | id          |                1 | int       |                 |
22. | greatsql     | tf_t2      | c1          |                2 | udt1      | udt_name="udt1" |
23. +--------------+------------+-------------+------------------+-----------+-----------------+

6. 导出备份

由于 CREATE TABLE OF TYPE 需要从 UDT 中继承用户自定义的数据类型，因此在使用 mysqldump 导出数据时，还需要再指定 --routines 选项（默认为关闭），把UDT也一并导出，否则会导致在恢复数据时失败。

示例：

1. mysqldump -S/data/GreatSQL/mysql.sock -uroot -pxxx -B greatsql --routines > /data/backup/GreatSQL/greatsql-ddl.sql

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx