创建一个MySQL数据库中的datetime类型

vlambda
2022-03-18

创建一个MySQL数据库中的datetime类型

环境

文档用途

详细信息

环境

系统平台：Microsoft Windows (64-bit) 10

版本：4.5

文档用途

介绍瀚高数据库中创建一个datetime类型的方法以及create domain 和create type的用法和区别。

详细信息

瀚高数据库中支持使用以下语句创建用户定义的数据类型：

CREATE DOMAIN：它创建了一个用户定义的数据类型，可以有可选的约束，基于其他基本类型，实质是定义一个域。
CREATE TYPE：它通常用于使用存储过程创建复合类型(两种或多种数据类型混合的数据类型)。

domain用法及示例

假如有以下表结构：

create table test_domain (id varchar,md5 text not null check(length(md5)=32));

（左右滑动查看完整内容）

其中md5列的类型及约束，可以定义一个domain来抽象，如下：

highgo=# create domain md5 as
highgo-# text not null
highgo-# check (
highgo(# length(value) = 32
highgo(# );
CREATE DOMAIN
highgo=# 


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 TABLE
highgo=# 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类型

highgo=# create domain datetime as timestamp without time zone;
highgo=# create table t_time (id int,create_time datetime);
CREATE TABLE
highgo=# \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: heap
highgo=# insert into t_time values (1,now()),(2,now());
INSERT 0 2
highgo=# 
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用法及示例

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

（左右滑动查看完整内容）

创建示例：

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);