存储空间消耗磁盘比较 int varchar date

时间：2019-03-16 20:33:25 阅读：168 评论：0 收藏：0 [点我收藏+]

小结：

1、日期类型按照date存储节省空间，仅3字节，而按照字符串型char 8字节 20190316 ， varchar 20190316 9字节；

2、对于小于32768的整数，按照smallint仅仅为2字节，较字符型节约空间；

3、时间戳 1552187447 用int ，而非char(12);整数ip，长ip，用bigint；能数字，不字符型；

2019-03-16 20190316

https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

Table 11.1 Required Storage and Range for Integer Types Supported by MySQL

Type	Storage (Bytes)	Minimum Value Signed	Minimum Value Unsigned	Maximum Value Signed	Maximum Value Unsigned
`TINYINT`	1	`-128`	`0`	`127`	`255`
`SMALLINT`	2	`-32768`	`0`	`32767`	`65535`
`MEDIUMINT`	3	`-8388608`	`0`	`8388607`	`16777215`
`INT`	4	`-2147483648`	`0`	`2147483647`	`4294967295`
`BIGINT`	8	`-2⁶³`	`0`	`2⁶³-1`	`2⁶⁴-1`

int是固定的4字节

>>> 2**32
4294967296
>>> 2**31
2147483648
>>>

>>> 2**15
32768
>>>

https://dev.mysql.com/doc/refman/8.0/en/char.html

Value	`CHAR(4)`	Storage Required	`VARCHAR(4)`	Storage Required
`‘‘`	`‘ ‘`	4 bytes	`‘‘`	1 byte
`‘ab‘`	`‘ab ‘`	4 bytes	`‘ab‘`	3 bytes
`‘abcd‘`	`‘abcd‘`	4 bytes	`‘abcd‘`	5 bytes
`‘abcdefgh‘`	`‘abcd‘`	4 bytes	`‘abcd‘`	5 bytes

The values shown as stored in the last row of the table apply only when not using strict mode; if MySQL is running in strict mode, values that exceed the column length are not stored, and an error results.

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-date-time

Date and Time Type Storage Requirements

For TIME, DATETIME, and TIMESTAMP columns, the storage required for tables created before MySQL 5.6.4 differs from tables created from 5.6.4 on. This is due to a change in 5.6.4 that permits these types to have a fractional part, which requires from 0 to 3 bytes.

Data Type	Storage Required Before MySQL 5.6.4	Storage Required as of MySQL 5.6.4
`YEAR`	1 byte	1 byte
`DATE`	3 bytes	3 bytes
`TIME`	3 bytes	3 bytes + fractional seconds storage
`DATETIME`	8 bytes	5 bytes + fractional seconds storage
`TIMESTAMP`	4 bytes	4 bytes + fractional seconds storage

As of MySQL 5.6.4, storage for YEAR and DATE remains unchanged. However, TIME, DATETIME, andTIMESTAMP are represented differently. DATETIME is packed more efficiently, requiring 5 rather than 8 bytes for the nonfractional part, and all three parts have a fractional part that requires from 0 to 3 bytes, depending on the fractional seconds precision of stored values.

Fractional Seconds Precision	Storage Required
0	0 bytes
1, 2	1 byte
3, 4	2 bytes
5, 6	3 bytes

For example, TIME(0), TIME(2), TIME(4), and TIME(6) use 3, 4, 5, and 6 bytes, respectively. TIME andTIME(0) are equivalent and require the same storage.

存储空间消耗磁盘比较 int varchar date

原文：https://www.cnblogs.com/yuanjiangw/p/10544050.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)