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

目录一、domain用法及示例二、创建MySQL中datetime类型三、createtype用法及示例环境系统平台：MicrosoftWindows(64-bit)10版本：4.5瀚高数据库...

目录

环境系统平台：Microsoft Windows (64-bit) 10版本：4.5

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

一、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类型内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！