MySQL建表时,日期时间类型选择

本文详细介绍了MySQL中支持的日期时间类型,包括DATETIME、TIMESTAMP、DATE、TIME和YEAR的特点及适用场景,并对比了它们之间的区别。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

MySQL(5.5)所支持的日期时间类型有:DATETIME、 TIMESTAMP、DATE、TIME、YEAR。

几种类型比较如下:

日期时间类型占用空间日期格式最小值最大值零值表示
 DATETIME 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:009999-12-31 23:59:59 0000-00-00 00:00:00
 TIMESTAMP 4 bytes YYYY-MM-DD HH:MM:SS 197001010800012038 年的某个时刻00000000000000
 DATE 4 bytes YYYY-MM-DD1000-01-01 9999-12-31 0000-00-00
 TIME 3 bytes HH:MM:SS -838:59:59838:59:59 00:00:00
 YEAR 1 bytes YYYY1901 2155 0000

 

 

 

 

 

DATETIME

DATETIME 用于表示 年月日 时分秒,是 DATE 和 TIME 的组合,并且记录的年份(见上表)比较长久。如果实际应用中有这样的需求,就可以使用 DATETIME 类型。

TIMESTAMP

--TIMESTAMP 用于表示 年月日 时分秒,但是记录的年份(见上表)比较短暂。

--TIMESTAMP 和时区相关,更能反映当前时间。当插入日期时,会先转换为本地时区后再存放;当查询日期时,会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是  不一样的。

--表中的第一个 TIMESTAMP 列自动设置为系统时间(CURRENT_TIMESTAMP)。当插入或更新一行,但没有明确给 TIMESTAMP 列赋值,也会自动设置为当前系统时间。如果表中有第二个 TIMESTAMP 列,则默认值设置为0000-00-00 00:00:00。

--TIMESTAMP 的属性受 Mysql 版本和服务器 SQLMode 的影响较大。

-如果记录的日期需要让不同时区的人使用,最好使用 TIMESTAMP。

DATE

DATE 用于表示 年月日,如果实际应用值需要保存 年月日 就可以使用 DATE。

TIME

TIME 用于表示 时分秒,如果实际应用值需要保存 时分秒 就可以使用 TIME。

YEAR

YEAR 用于表示 年份,YEAR 有 2 位(最好使用4位)和 4 位格式的年。 默认是4位。如果实际应用只保存年份,那么用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间,还能提高表的操作效率。

-----------------------

 

The DATE, DATETIME, and TIMESTAMP types are related. This section describes their characteristics, how they are similar, and how they differ. MySQL recognizes DATE, DATETIME, and TIMESTAMP values in several formats. For the DATE and DATETIME range descriptions, “supported” means that although earlier values might work, there is no guarantee.

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is 'YYYY-MM-DD HH:MM:SS[.fraction]', the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL.

The TIMESTAMP and DATETIME data types offer automatic initialization and updating to the current date and time. For more information.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval(TIMESTAMP可以做时区转换). (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00'). 

 

### 关于 MySQL 中 `CREATE TABLE` 的数据类型MySQL 中,当通过 `CREATE TABLE` 命令定义,可以指定每列的数据类型。这些数据类型决定了该列能够存储何种类型的值以及其范围或精度。以下是常见的 MySQL 数据类型分类及其具体类型[^1]: #### 数字类型 (Numeric Types) 用于存储整数和浮点数值。 - **TINYINT**: 小整型 (-128 到 127 或者 0 到 255 如果是无符号)。 - **SMALLINT**: 短整型 (-32768 到 32767 或者 0 到 65535 如果是无符号)。 - **MEDIUMINT**: 中等长度的整数 (-8388608 到 8388607 或者 0 到 16777215 如果是无符号)。 - **INT/INTEGER**: 标准整数 (-2147483648 到 2147483647 或者 0 到 4294967295 如果是无符号)。 - **BIGINT**: 大整数 (-9223372036854775808 到 9223372036854775807 或者 0 到 18446744073709551615 如果是无符号)。 #### 浮点数类型 - **FLOAT(M,D)**: 单精度浮点数,默认精度为单精度。 - **DOUBLE(M,D)**: 双精度浮点数,默认精度为双精度。 - **DECIMAL(M,D)/NUMERIC(M,D)**: 定点数,其中 M 是总位数,D 是小数部分的位数。 #### 字符串类型 (String Types) 字符串类型用于存储字符或者二进制数据。 - **CHAR(N)**: 固定长度字符串,N 示最大长度(最多 255 个字符)。 - **VARCHAR(N)**: 可变长度字符串,N 示最大长度(最多 65535 个字符)。 - **TEXT/BLOB**: 存储大文本对象,分别有 TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT 类型;BLOB 对应的是二进制大型对象。 #### 间日期类型 (Date and Time Types) 间日期类型专门设计来处理日期和间信息。 - **DATE**: YYYY-MM-DD 格式的日期值。 - **DATETIME**: YYYY-MM-DD HH:MM:SS 格式的日期时间间组合。 - **TIMESTAMP**: UNIX 间戳示的间,通常也采用 YYYY-MM-DD HH:MM:SS 形式显示。 - **TIME**: HH:MM:SS 格式的刻。 - **YEAR**: 年份,支持两种格式 YEAR(2) 和 YEAR(4),默认为后者。 创建还可以设置某些属性比如是否允许为空(`NULL`)还是强制非空(`NOT NULL`),是否有默认值(DEFAULT value)[^2]。 另外需要注意,在命名新候应该遵循一定的规则以避免冲突或者是语法错误的发生,例如不可使用保留关键字作为名字等等[^3]。 对于特殊用途的需求,MySQL 提供了临选项TEMPORARY,这种格仅限于当前会话期间有效,并且一旦断开客户端链接就会被销毁掉[^4]。 ```sql -- 创建带多种数据类型的简单例子 CREATE TABLE example_table ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age SMALLINT UNSIGNED, salary DECIMAL(10,2), hire_date DATE ); ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值