Oracle兼容-语法-TABLE UDT

1. 语法

2. 1. CREATE TABLE table_name(column_name type, column_name type ...)
3. type：nomally type/udt type:[db.]type(不支持varray/table类型udt).

5. 2. INSERT INTO table_name VALUES(values)
6. values:nomally value/udt value
7. udt value:[db.]udt_name(values)

9. 3.  ALTER TABLE table_name DROP/ADD [column] column_name udt_type
10. udt_type:[db.]udt_name(values)

12. 4. set @@udt_format_result=['BINARY'/'DBA'];

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

2. 定义和用法

GreatSQL支持用户自定义TABLE类型，支持以下几种用法：

  • 语法1：执行 CREATE TABLE(udt_name udt_type) 创建UDT列。
  • 语法2：向UDT列插入UDT值。
  • 语法3：添加和删除UDT列(注：alter不支持modify操作)。
  • 语法4：可设置 udt_format_result 会话选项指定UDT类型数据的输出格式。

GreatSQL的TABLE UDT有以下几条限制：

  1. TABLE UDT不能跨Schema使用。
  2. 在ORACLE模式下，关键词 TYPE 不能作为Schema名或User名。
  3. 目前只支持 name = udt_type() 函数，UDT列不支持用在其他内置函数。
  4. UDT列不支持 TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB 等几个数据类型。

3. Oracle兼容说明

GreatSQL中的TABLE UDT与Oracle兼容情况说明如下：

  1. 不支持对UDT列设定默认值，也不支持设置为 PRIMARY KEY/FOREIGN KEY/CONSTRAINT/NULL/NOT NULL/INVISIBLE/VIRTUAL 等属性。
  2. 只允许对UDT列插入UDT值，其他类型值不允许插入UDT列；UDT值也不允许插入其他类型列。
  3. 不支持对UDT列执行 CREATE INDEX/ORDER BY/GROUP BY/PARTITION BY/CREATE VIEW/ALTER MODIFY 等操作行为。
  4. 只能添加和删除UDT列，不支持 MODIFY 修改UDT列。
  5. 支持 CREATE VIEW，mysql.routines的table_count值不改变。区别是如果使用udt type的table删除而view没有删除，也支持删除该udt type。
  6. UDT列的成员列不允许单独使用在独立的SQL语句中，比如 udt_type.id 这种用法（Oracle支持该用法）。
  7. UDT列不支持用在大部分内置函数中，除了在查询条件中用于比大小以及 ISNULL/IS NOT NULL，比如 WHERE c1 = udt_type1(1, 'r1')。
  8. UDT列不支持 CREATE TEMPORARY TABLE 用法。
  9. 执行 SHOW CREATE TABLE WITH udt_type 显示的UDT列类型就是TYPE的名字。
  10. 选项udt_format_result 默认值为 BINARY，即输出结果显式为BINARY格式，否则正常显示格式（udt_format_result = DBA）。
  11. 不支持 BLOB/MEDIUM_BLOB/LONG_BLOB/TINY_BLOB 等数据类型。
  12. 对于跨库表的操作，要求UDT type和UDT table指定的Schema名一致，具体如下：
1. greatsql> use greatsql;

3. -- 下例中的 udt_type1 等同于 greatsql.udt_type1
4. -- 此时t1会创建失败，因为t1在db2中，而udt_type1在greatsql中，不在同一个Schema
5. greatsql> CREATE TABLE db2.t1(id INT, u1 udt_type1);

7. -- 下例中 udt_type1 等同于 greatsql.udt_type1
8. -- 此时插入失败，因为t1在db2中，而udt_type1在greatsql中，不在同一个Schema
9. greatsql> INSERT INTO db2.t1 VALUES(1, udt_type1(1, 'a'));这里的udt_type1指的是db1.udt_type1.

11. -- 需要修改成下面这样
12. greatsql> INSERT INTO db2.t1 VALUES(1, db2.udt_type1(1, 'a'));

14. -- 这里指的是db1.udt_type1，如果想成功创建，需要指定db2.udt_type1
15. greatsql> ALTER TABLE db2.t1 ADD c2 udt_type1;
  1. 插入语句 INSERT INTO t1 VALUES udt_type(NULL) 和 INSERT INTO t1 VALUES(NULL) 实际写入值是不一样的，写入完后执行 SELECT * FROM t1 WHERE udt_type IS NULL 查询的结果是后者，而非前者。
  2. 在类似 SELECT * FROM t1 WHERE udt_type1 [<|=|>] udt_type1 的比较查询中，如果是字符串则会按照 BINARY 格式进行比较；而在Oracle中是当小于或者大于的时候直接报错，而等于的时候直接返回空值。这点与Oracle不一致。
  3. 在类似 SELECT * FROM t1 WHERE udt_type1 [<|=|>] udt_type2 的比较查询中，由于 udt_type1 的 udt name 不等于 udt_type2 的 udt name，所以会报错；而Oracle是在等于查询的时候直接返回空值，这点与Oracle不一致。
  4. 通过查询系统表 information_schema.COLUMNS 的 extra 列中是否带有 udt_name 信息，就可知道哪些表的列带有udt类型。例如：
1. greatsql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, EXTRA FROM information_schema.COLUMNS WHERE TABLE_NAME = 'udt_t1';
2. +--------------+------------+-------------+-----------+-----------------+
3. | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DATA_TYPE | EXTRA           |
4. +--------------+------------+-------------+-----------+-----------------+
5. | greatsql     | udt_t1     | id          | int       |                 |
6. | greatsql     | udt_t1     | c1          | udt1      | udt_name="udt1" |
7. +--------------+------------+-------------+-----------+-----------------+
8. 2 rows in set (0.00 sec)

4. 示例

    1. 示例1：CREATE TABLE 用法
1. greatsql> SET sql_mode = ORACLE;

3. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 CHAR(20));
4. greatsql> CREATE TABLE udt_t1(id INT, c1 udt1);

7. -- 不支持的用法
8. greatsql> CREATE OR REPLACE TYPE udt_t1 IS TABLE OF INT;
9. greatsql> CREATE TABLE t1(id INT, c1 udt_t1);
10. ERROR 1235 (42000): This version of MySQL doesn't yet support 'create table with udt table'
    1. 示例2：INSERT INTO TABLE 用法
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> set udt_format_result = 'DBA';

4. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 CHAR(20));
5. greatsql> CREATE TABLE udt_t1(id INT, c1 udt1);
6. greatsql> INSERT INTO udt_t1 VALUES(1, udt1(1, 'c1_row1'));
7. greatsql> SELECT * FROM udt_t1;
8. +------+-------------------+
9. | id   | c1                |
10. +------+-------------------+
11. |    1 | id:1 | c1:c1_row1 |
12. +------+-------------------+
13. 1 row in set (0.00 sec)
    1. 示例3：ALTER TABLE ADD
1. greatsql> SET SQL_MODE=ORACLE;

3. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 CHAR(20));
4. greatsql> CREATE TABLE udt_t1(id INT, c1 udt1);

6. greatsql> ALTER TABLE udt_t1 ADD c2 udt1;
7. Query OK, 0 rows affected (0.00 sec)
8. Records: 0  Duplicates: 0  Warnings: 0

10. greatsql> SHOW CREATE TABLE udt_t1\G
11. *************************** 1. row ***************************
12. Table: udt_t1
13. Create Table: CREATE TABLE `udt_t1` (
14. `id` int DEFAULT NULL,
15. `c1` udt1 DEFAULT NULL,
16. `c2` udt1 DEFAULT NULL
17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
18. 1 row in set (0.00 sec)
    1. 示例4：ALTER TABLE DROP
1. greatsql> SET SQL_MODE=ORACLE;

3. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 CHAR(20));
4. greatsql> CREATE TABLE udt_t1(id INT, c1 udt1);

6. greatsql> ALTER TABLE udt_t1 ADD c2 udt1;
7. greatsql> ALTER TABLE udt_t1 DROP c2;

9. greatsql> SHOW CREATE TABLE udt_t1\G
10. *************************** 1. row ***************************
11. Table: udt_t1
12. Create Table: CREATE TABLE `udt_t1` (
13. `id` int DEFAULT NULL,
14. `c1` udt1 DEFAULT NULL
15. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    1. 示例5：DROP TABLE/DROP TYPE
1. greatsql> SET sql_mode = ORACLE;

3. -- 删除UDT前，要先删除关联的表，否则会失败
4. greatsql> DROP TYPE udt1;
5. ERROR 7549 (HY000): Failed to drop type t_air with type or table dependents.

7. greatsql> DROP TABLE udt_t1;
8. Query OK, 0 rows affected (0.00 sec)

10. greatsql> DROP TYPE udt1;
11. Query OK, 0 rows affected (0.00 sec)
    1. 示例6：CREATE VIEW
1. greatsql> SET sql_mode = ORACLE;

3. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 CHAR(20));
4. greatsql> CREATE TABLE udt_t1(id INT, c1 udt1);

6. greatsql> CREATE VIEW v1 AS SELECT * FROM udt_t1;
7. greatsql> SHOW CREATE VIEW v1\G
8. *************************** 1. row ***************************
9. View: v1
10. Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS SELECT `udt_t1`.`id` AS `id`,`udt_t1`.`c1` AS `c1` from `udt_t1`
11. character_set_client: utf8mb4
12. collation_connection: utf8mb4_0900_ai_ci
    1. 示例7：查询与函数
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';

4. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 CHAR(20));
5. greatsql> CREATE TABLE udt_t1(id INT, c1 udt1);

7. greatsql> INSERT INTO udt_t1 VALUES(1, udt1(1, 'c1_row1')), (2, udt1(2, 'c1_row2'));

9. greatsql> SELECT * FROM udt_t1;
10. +------+-------------------+
11. | id   | c1                |
12. +------+-------------------+
13. |    1 | id:1 | c1:c1_row1 |
14. |    2 | id:2 | c1:c1_row2 |
15. +------+-------------------+
16. 2 rows in set (0.00 sec)

18. greatsql> SELECT * FROM udt_t1 WHERE c1 = udt1(1, 'c1_row1');
19. +------+-------------------+
20. | id   | c1                |
21. +------+-------------------+
22. |    1 | id:1 | c1:c1_row1 |
23. +------+-------------------+
24. 1 row in set (0.00 sec)

26. greatsql> SELECT c1 || udt1(3, 'c1_row3') FROM udt_t1;
27. ERROR 1235 (42000): This version of MySQL doesn't yet support 'udt type columns used in function'

29. greatsql> SELECT c1 FROM udt_t1 GROUP BY c1;
30. ERROR 7548 (HY000): cannot ORDER objects without window function or ORDER method

32. greatsql> ALTER TABLE udt_t1 ADD c2 udt1;
33. greatsql> SELECT * FROM udt_t1;
34. +------+-------------------+------+
35. | id   | c1                | c2   |
36. +------+-------------------+------+
37. |    1 | id:1 | c1:c1_row1 | NULL |
38. |    2 | id:2 | c1:c1_row2 | NULL |
39. +------+-------------------+------+
40. 2 rows in set (0.00 sec)

42. greatsql> SELECT * FROM udt_t1 WHERE c2 IS NULL;
43. +------+-------------------+------+
44. | id   | c1                | c2   |
45. +------+-------------------+------+
46. |    1 | id:1 | c1:c1_row1 | NULL |
47. |    2 | id:2 | c1:c1_row2 | NULL |
48. +------+-------------------+------+
49. 2 rows in set (0.00 sec)

51. greatsql> SELECT * FROM udt_t1 WHERE ISNULL(c2);
52. +------+-------------------+------+
53. +------+-------------------+------+
54. | id   | c1                | c2   |
55. +------+-------------------+------+
56. |    1 | id:1 | c1:c1_row1 | NULL |
57. |    2 | id:2 | c1:c1_row2 | NULL |
58. +------+-------------------+------+

60. greatsql> UPDATE udt_t1 SET c2 = c1 WHERE id = 1;
61. Query OK, 1 row affected (0.00 sec)
62. Rows matched: 1  Changed: 1  Warnings: 0

64. greatsql> SELECT * FROM udt_t1;
65. +------+-------------------+-------------------+
66. | id   | c1                | c2                |
67. +------+-------------------+-------------------+
68. |    1 | id:1 | c1:c1_row1 | id:1 | c1:c1_row1 |
69. |    2 | id:2 | c1:c1_row2 | NULL              |
70. +------+-------------------+-------------------+
71. 2 rows in set (0.00 sec)

73. greatsql> SELECT c2 = c1, c2 > c1, c2 < c1 FROM udt_t1;
74. +---------+---------+----------+
75. | c2 = c1 | c2 > c1 | c2 < c1  |
76. +---------+---------+----------+
77. |       1 |       0 |        0 |
78. |    NULL |    NULL |     NULL |
79. +---------+---------+----------+
80. 2 rows in set (0.00 sec)
    1. 示例8：UPDATE SET
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';

4. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 CHAR(20));
5. greatsql> CREATE TABLE udt_t1(id INT, c1 udt1);

7. greatsql> INSERT INTO udt_t1 VALUES(1, udt1(1, 'c1_row1')), (2, udt1(2, 'c1_row2'));

9. greatsql> SELECT * FROM udt_t1;
10. +------+-------------------+
11. | id   | c1                |
12. +------+-------------------+
13. |    1 | id:1 | c1:c1_row1 |
14. |    2 | id:2 | c1:c1_row2 |
15. +------+-------------------+
16. 2 rows in set (0.00 sec)

18. greatsql> UPDATE udt_t1 SET c1 = udt1(50, 'c1_row1') WHERE id = 1;
19. Query OK, 1 row affected (0.00 sec)
20. Rows matched: 1  Changed: 1  Warnings: 0

22. greatsql> SELECT * FROM udt_t1;
23. +------+--------------------+
24. | id   | c1                 |
25. +------+--------------------+
26. |    1 | id:50 | c1:c1_row1 |
27. |    2 | id:2 | c1:c1_row2  |
28. +------+--------------------+
29. 2 rows in set (0.00 sec)
    1. 示例9：CURSOR
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';

4. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 CHAR(20));
5. greatsql> CREATE TABLE udt_t1(id INT, c1 udt1);

7. greatsql> INSERT INTO udt_t1 VALUES(1, udt1(1, 'c1_row1')), (2, udt1(2, 'c1_row2'));

9. greatsql> SELECT * FROM udt_t1;
10. +------+-------------------+
11. | id   | c1                |
12. +------+-------------------+
13. |    1 | id:1 | c1:c1_row1 |
14. |    2 | id:2 | c1:c1_row2 |
15. +------+-------------------+
16. 2 rows in set (0.00 sec)

18. greatsql> DELIMITER //
19. greatsql> CREATE OR REPLACE PROCEDURE p1() AS
20. CURSOR cur1 (i_id INT) IS SELECT * FROM udt_t1 WHERE id = i_id;
21. rec cur1%ROWTYPE;
22. BEGIN
23. FOR rec IN cur1(2)
24. LOOP
25. SELECT rec.id, rec.c1;
26. END LOOP;
27. END; //

29. greatsql> CALL p1() //
30. +--------+-------------------+
31. | rec.id | rec.c1            |
32. +--------+-------------------+
33. |      2 | id:2 | c1:c1_row2 |
34. +--------+-------------------+
35. 1 row in set (0.00 sec)

37. Query OK, 0 rows affected (0.00 sec)
    1. 示例10：%TYPE
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';

4. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 CHAR(20));
5. greatsql> CREATE TABLE udt_t1(id INT, c1 udt1);

7. greatsql> INSERT INTO udt_t1 VALUES(1, udt1(1, 'c1_row1')), (2, udt1(2, 'c1_row2'));

9. greatsql> SELECT * FROM udt_t1;
10. +------+-------------------+
11. | id   | c1                |
12. +------+-------------------+
13. |    1 | id:1 | c1:c1_row1 |
14. |    2 | id:2 | c1:c1_row2 |
15. +------+-------------------+
16. 2 rows in set (0.00 sec)

18. greatsql> DELIMITER //
19. greatsql> CREATE OR REPLACE PROCEDURE p1(min INT,max INT) AS
20. CURSOR cur(pmin INT, pmax INT) IS SELECT c1 FROM udt_t1 WHERE id BETWEEN pmin AND pmax;
21. v_c1 udt_t1.c1%TYPE;
22. BEGIN
23. OPEN cur(min, max);
24. LOOP
25. FETCH cur INTO v_c1;
26. EXIT WHEN cur%NOTFOUND;
27. SELECT v_c1;
28. END LOOP;
29. CLOSE cur;
30. END; //

32. greatsql> CALL p1(1,10) //
33. +-------------------+
34. | v_c1              |
35. +-------------------+
36. | id:1 | c1:c1_row1 |
37. +-------------------+
38. 1 row in set (0.00 sec)

40. +-------------------+
41. | v_c1              |
42. +-------------------+
43. | id:2 | c1:c1_row2 |
44. +-------------------+
45. 1 row in set (0.00 sec)

47. Query OK, 0 rows affected (0.00 sec)
    1. 示例11：udt_format_result

客户端建立新连接，加上参数 --binary-as-hex：

1. $ mysql -A -S/data/GreatSQL/mysql.sock --binary-as-hex greatsql

3. -- 建立连接时，加上--binary-as-hex就会有下面的标识
4. greatsql> \s
5. ...
6. Binary data as:         Hexadecimal
7. ...

9. -- 参数udt_format_result默认值为BINARY
10. greatsql> SELECT @@udt_format_result;
11. +---------------------+
12. | @@udt_format_result |
13. +---------------------+
14. | BINARY              |
15. +---------------------+

17. greatsql> SELECT * FROM udt_t1;
18. +------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
19. | id   | c1                                                                                                                                                                           |
20. +------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
21. |    1 | 0xFC0100000063315F726F773120202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020 |
22. |    2 | 0xFC0200000063315F726F773220202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020 |
23. +------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
24. 2 rows in set (0.00 sec)

26. -- 修改 udt_format_result = 'DBA'
27. greatsql> SET udt_format_result = 'DBA';

29. greatsql> SELECT * FROM udt_t1;
30. +------+-------------------+
31. | id   | c1                |
32. +------+-------------------+
33. |    1 | id:1 | c1:c1_row1 |
34. |    2 | id:2 | c1:c1_row2 |
35. +------+-------------------+
36. 2 rows in set (0.00 sec)

5. TABLE UDT数据字典

1. -- 1. 查询 information_schema.ROUTINES 查看所有 UDT
2. greatsql> SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME = 'UDT1'\G
3. *************************** 1. row ***************************
4. SPECIFIC_NAME: udt1
5. ROUTINE_CATALOG: def
6. ROUTINE_SCHEMA: greatsql
7. ROUTINE_NAME: udt1
8. ROUTINE_TYPE: TYPE
9. DATA_TYPE:
10. CHARACTER_MAXIMUM_LENGTH: NULL
11. CHARACTER_OCTET_LENGTH: NULL
12. NUMERIC_PRECISION: NULL
13. NUMERIC_SCALE: NULL
14. DATETIME_PRECISION: NULL
15. CHARACTER_SET_NAME: NULL
16. COLLATION_NAME: NULL
17. DTD_IDENTIFIER: NULL
18. ROUTINE_BODY: SQL
19. ROUTINE_DEFINITION:
20. EXTERNAL_NAME: NULL
21. EXTERNAL_LANGUAGE: SQL
22. PARAMETER_STYLE: SQL
23. IS_DETERMINISTIC: NO
24. SQL_DATA_ACCESS: CONTAINS SQL
25. SQL_PATH: NULL
26. SECURITY_TYPE: DEFINER
27. CREATED: 2023-11-21 13:51:40
28. LAST_ALTERED: 2023-11-21 13:51:47
29. SQL_MODE: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ORACLE,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
30. ROUTINE_COMMENT:
31. DEFINER: root@localhost
32. CHARACTER_SET_CLIENT: utf8mb4
33. COLLATION_CONNECTION: utf8mb4_0900_ai_ci
34. DATABASE_COLLATION: utf8mb4_0900_ai_ci
35. 1 row in set (0.00 sec)

37. -- 2. 查询 information_schema.PARAMETERS 查看UDT定义
38. greatsql> SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'udt1'\G
39. *************************** 1. row ***************************
40. SPECIFIC_CATALOG: def
41. SPECIFIC_SCHEMA: greatsql
42. SPECIFIC_NAME: udt1
43. ORDINAL_POSITION: 1
44. PARAMETER_MODE: IN
45. PARAMETER_NAME: id
46. DATA_TYPE: int
47. CHARACTER_MAXIMUM_LENGTH: NULL
48. CHARACTER_OCTET_LENGTH: NULL
49. NUMERIC_PRECISION: 10
50. NUMERIC_SCALE: 0
51. DATETIME_PRECISION: NULL
52. CHARACTER_SET_NAME: NULL
53. COLLATION_NAME: NULL
54. DTD_IDENTIFIER: int
55. ROUTINE_TYPE: TYPE
56. *************************** 2. row ***************************
57. SPECIFIC_CATALOG: def
58. SPECIFIC_SCHEMA: greatsql
59. SPECIFIC_NAME: udt1
60. ORDINAL_POSITION: 2
61. PARAMETER_MODE: IN
62. PARAMETER_NAME: c1
63. DATA_TYPE: char
64. CHARACTER_MAXIMUM_LENGTH: 20
65. CHARACTER_OCTET_LENGTH: 80
66. NUMERIC_PRECISION: NULL
67. NUMERIC_SCALE: NULL
68. DATETIME_PRECISION: NULL
69. CHARACTER_SET_NAME: utf8mb4
70. COLLATION_NAME: utf8mb4_0900_ai_ci
71. DTD_IDENTIFIER: char(20)
72. ROUTINE_TYPE: TYPE
73. 2 rows in set (0.00 sec)

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx