MySQL integer/date/time type description

MySQL integer/date/time type description MySQL Integer type: storage space occupied, Integer Range comparison. MySQL date type: Date Format, storage space occupied, and date range comparison. MySQL time type: Time Format, storage space occupied, and time range. I. mysql integer types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Use 8, 16, 24, 32, and 64-bit buckets respectively. MySQL Integer type: storage space occupied and Integer Range comparison. Integer-type Bucket unsigned integer range signed Integer Range

------------  ---------   ------------------------  ----------------------------------------- tinyint        8  bits   0 ~ 255                   -128 ~ 127 smallint       16 bits   0 ~ 65535                 -32768  ~ 32767 mediumint      24 bits   0 ~ 16777216              -8388608 ~ 8388607 int            32 bits   0 ~ 4294967295            -2147483648 ~ 2147483647 bigint         64 bits   0 ~ 18446744073709551615  -9223372036854775808 ~ 9223372036854775807

 

The range of stored values is-2 (N-1) to 2 (N-1)-1, and the unsigned storage range is 0 to 2N-1. Mysql can specify the width for the integer type, for example, int (11). This is meaningless for most applications: it does not limit the valid range of values, it only requires some mysql interaction tools (such as the mysql command line client) to display the number of characters. For storage and computing, int (1) and int (20) are the same. 2. mysql date type: MySQL date type: Date Format, storage space occupied, and date range comparison. Date type storage space date format date range

------------  ---------   --------------------- ----------------------------------------- datetime       8 bytes   YYYY-MM-DD HH:MM:SS   1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 timestamp      4 bytes   YYYY-MM-DD HH:MM:SS   1970-01-01 00:00:01 ~ 2038 date           3 bytes   YYYY-MM-DD            1000-01-01          ~ 9999-12-31 year           1 bytes   YYYY                  1901                ~ 2155

 

Columns of the timestamp type have another feature: by default, the timestamp column is automatically filled/updated with the current time (CURRENT_TIMESTAMP) during insert and update data. "Automatic" means that MySQL will handle it for you without worrying about it. The value displayed in timestamp depends on the time zone, mysql server, operating system, and client connection. The value displayed by datetime is irrelevant to the time zone, and the date and time represented by the text are retained. Iii. mysql time type: MySQL time type: Time Format, storage space occupied, and time range. Time Format time range ------------ --------- --------------------------- --------------------------------------------- time 3 bytes HH: MM: SS-838: 59: 59 ~ 838: 59: 59 the time range has such a large range. In particular, the time value can be negative, which is a bit strange. Later, I read the MySQL manual to know that this was designed to meet the requirements of two date-time subtraction.

select timediff('2000:01:31 23:59:59', '2000:01:01 00:00:00');  --  743:59:59select timediff('2000:01:01 00:00:00', '2000:01:31 23:59:59');  -- -743:59:59select timediff('23:59:59', '12:00:00');                        --  11:59:59

 

Note: The two timediff parameters must be of the datetime, timestamp, and time types, and must be the same. That is, compare datetime/timestamp with datetime/timestamp; compare time with time. Although MySQL has a wide range of Date and Time types, it is a pity that currently () These Date and Time types can only be in seconds, not milliseconds, microseconds. No function is generated in milliseconds.