如何创建一个创建MySQL数据库中的datetime类型
短信预约 -IT技能 免费直播动态提醒
环境系统平台:Microsoft Windows (64-bit) 10版本:4.5
瀚高数据库中支持使用以下语句创建用户定义的数据类型:
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);
到此这篇关于如何创建一个创建MySQL数据库中的datetime类型的文章就介绍到这了,更多相关创建datetime类型内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341