环境系统平台：Microsoft Windows (64-bit) 10版本：4.5
瀚高数据库中支持使用以下语句创建用户定义的数据类型：
-
CREATE DOMAIN：它创建了一个用户定义的数据类型，可以有可选的约束，基于其他基本类型，实质是定义一个域。 -
CREATE TYPE：它通常用于使用存储过程创建复合类型(两种或多种数据类型混合的数据类型)。
一、domain用法及示例
假如有以下表结构：
|
1
|
create table test_domain (id varchar,md5 text not null check(length(md5)=32)); |
其中md5列的类型及约束，可以定义一个domain来抽象，如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
highgo=# create domain md5 ashighgo-# text not nullhighgo-# check (highgo(# length(value) = 32highgo(# );CREATE DOMAINhighgo=# highgo=# \dD md5 List of domains Schema | Name | Type | Collation | Nullable | Default | Check --------+------+------+-----------+----------+---------+---------------------------- public | md5 | text | | not null | | CHECK (length(VALUE) = 32)(1 row)highgo=# create table test_domain (id varchar,md5 md5);CREATE TABLEhighgo=# insert into test_domain values('1','2');ERROR: value for domain md5 violates check constraint "md5_check"highgo=# insert into test_domain values('2','76a2173be6393254e72ffa4d6df1030a');INSERT 0 1 |
二、创建MySQL中datetime类型
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
highgo=# create domain datetime as timestamp without time zone;highgo=# create table t_time (id int,create_time datetime);CREATE TABLEhighgo=# \d+ t_time Table "public.t_time" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------+----------+-----------+----------+---------+---------+--------------+------------- id | integer | | | | plain | | create_time | datetime | | | | plain | | Access method: heaphighgo=# insert into t_time values (1,now()),(2,now());INSERT 0 2highgo=# highgo=# select * from t_time; id | create_time ----+---------------------------- 1 | 2021-08-03 19:28:11.207324 2 | 2021-08-03 19:28:11.207324(2 rows) |
三、create type用法及示例
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
CREATE TYPE name AS ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )CREATE TYPE name AS ENUM ( [ 'label' [, ... ] ] )CREATE TYPE name AS RANGE ( SUBTYPE = subtype [ , SUBTYPE_OPCLASS = subtype_operator_class ] [ , COLLATION = collation ] [ , CANONICAL = canonical_function ] [ , SUBTYPE_DIFF = subtype_diff_function ])CREATE TYPE name ( INPUT = input_function, OUTPUT = output_function [ , RECEIVE = receive_function ] [ , SEND = send_function ] [ , TYPMOD_IN = type_modifier_input_function ] [ , TYPMOD_OUT = type_modifier_output_function ] [ , ANALYZE = analyze_function ] [ , INTERNALLENGTH = { internallength | VARIABLE } ] [ , PASSEDBYVALUE ] [ , ALIGNMENT = alignment ] [ , STORAGE = storage ] [ , LIKE = like_type ] [ , CATEGORY = category ] [ , PREFERRED = preferred ] [ , DEFAULT = default ] [ , ELEMENT = element ] [ , DELIMITER = delimiter ] [ , COLLATABLE = collatable ])CREATE TYPE name |
创建示例：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
CREATE TYPE compfoo AS (f1 int, f2 text);CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$ SELECT fooid, fooname FROM foo$$ LANGUAGE SQL;CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');CREATE TABLE bug ( id serial, description text, status bug_status);CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi); |
到此这篇关于如何创建一个创建MySQL数据库中的datetime类型的文章就介绍到这了,更多相关创建datetime类型内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://blog.51cto.com/u_13646489/5116455
