Summary of MySQL Field Types

Value range of various fields in MySQL

Time: 20:59:14 Source: techtarget.com.cn Author: techtarget.com.cn

 

Tinyint

-128-127

Tinyint unsigned

0-255

Smallint

-32768-32767

Smallint unsigned

0-65535

Mediumint

-8388608-8388607

Mediumint unsigned

0-16777215

Int or integer

-2147483648-2147483647

Int unsigned or integer unsigned

0-4294967295

Bigint

-9223372036854775808-9223372036854775807

Bigint unsigned

0-18446744073709551615

Float

-3.402823466e + 38--1.175494351e-38

0

1.175494351e-38-3.402823466e + 38

Double, double precision, or real

-1.7976931348623157e + 308--2.225074255072014e-308

0

2.225074255072014e-308-1.7976931348623157e + 308

Decimal [(m, [d])] or numeric (M, d)

It is determined by M (the length of the entire number, including the decimal point, the number of digits on the left of the decimal point, the number of digits on the right of the decimal point, but not the negative number) and D (the number of digits on the right of the decimal point, M is 10 by default, D is 0 by default

Date

1000-01-01-9999-12-31

Datetime

1000-01-01 00:00:00-9999-12-31 23:59:59

Timestamp

2037 00:00:00-one day in December (I don't know which day, huh, huh)

Time

-838: 59: 59' to 838: 59: 59

Year [(2 | 4)]

The default format is 4 bits. The value range of 4 bits is 1901-1970, 2069, and the value range of 2 bits is 70-69)

Char (m) [binary] Or nchar (m) [binary]

M ranges from 1 to 255. If binary is not available, it is case-insensitive. nchar indicates that the default character set is used. fill in space in the database, but the space at the end of the removal will be automatically removed.

[National] varchar (m) [binary]

M ranges from 1 to 255. spaces at the end of the database will be removed automatically.

Tinyblob or tinytext

255 (2 ^ 8-1) characters

Blob or text

65535 (2 ^ 16-1) characters

Mediumblob or mediumtext

16777215 (2 ^ 24-1) characters

Longblob or longtext

4294967295 (2 ^ 32-1) characters

Enum ('value1', 'value2 ',...)

There can be a total of 65535 different values

Set ('value1', 'value2 ',...)

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

 

 

Do not update MySQL timestamp columns automatically

 

MySQL has a timestamp column type that can automatically Insert the current timestamp for the column. When the update statement is executed, the timestamp is automatically updated. But sometimes, you don't want to update the value automatically when it is updated. What should you do?

In fact, it is quite simple. timestamp itself supports this setting.

When a timestamp column is created, the general statement is:

Create Table tablename (datecolname timestamp not null ,........)

In fact, the complete statement of this statement is:

Create Table tablename (datecolname timestamp not null default current_timestamp on update current_timetamp ,........)

If you want to make the timestamp not to be updated automatically when the row value changes, you can use the following statement:

Create Table tablename (datecolname timestamp not null default current_timestamp ,........)

The key to the problem is that the default current_timestamp statement does not have the on update current_timestamp statement.

When creating a table structure in MySQL query browser, if you do not want timestamp to be automatically updated, remember to manually enter default as current_timestamp, in this way, MySQL query browser will automatically generate appropriate SQL statements to create the table structure.

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

MySQL Timestamp  

MySQL currently does not support column default as a function,
If the default value of a column is the current update date and time,
You can use the timestamp column type
The following describes the timestamp column type in detail.

Timestamp column type
The timestamp value can start from 1970 to 2037, and is precise to one second. Its value is displayed as a number.
The format of the timestamp value display size is as follows:
:
+ --------------- + ---------------- +
| Column type | display format |
| Timestamp (14) | yyyymmddhhmmss |
| Timestamp (12) | yymmddhhmmss |
| Timestamp (10) | yymmddhhmm |
| Timestamp (8) | yyyymmdd |
| Timestamp (6) | yymmdd |
| Timestamp (4) | yymm |
| Timestamp (2) | YY |
+ --------------- + ---------------- +
The "complete" timestamp format is 14 bits, but the timestamp column can also be created with a shorter display size.
The most common Display sizes are 6, 8, 12, and 14.
You can specify an arbitrary display size when creating a table, but the defined column length is 0 or greater than 14, and the column length is forcibly defined as 14.
The column length ranges from 1 ~ The dimensions of odd values in the range of 13 are forced to be the next larger even number.

Example:
Define Field Length Force Field Length
Timestamp (0)-> timestamp (14)
Timestamp (15)-> timestamp (14)
Timestamp (1)-> timestamp (2)
Timestamp (5)-> timestamp (6)

All timestamp columns have the same storage size,
Use the complete precision (14 bits) of the specified period time value to store valid values regardless of the display size.
An invalid date will be forcibly stored as 0
There are several meanings:
1. Although the column timestamp (8) is defined during table creation, the timestamp column is used when you insert and update data.
In fact, 14-bit data (including year, month, day, hour, minute, and second) is saved ),
However, during your query, MySQL returns 8-bit year, month, and day data.
If you use alter table to broaden a narrow timestamp column, previously "concealed" information will be displayed.
2. Similarly, narrowing down a timestamp column will not cause loss of information, except when the upper value is displayed, less information is displayed.
3. Although the timestamp value is stored as a complete precision, the only function for directly operating the stored value is unix_timestamp ();
Because the column value returned by MySQL In the timestamp column is the value retrieved after formatting,
This means that you may not be able to use certain functions to operate the timestamp column (such as hour () or second ()),
Unless the related part of the timestamp value is included in the formatted value.
For example, if a timestamp column is defined as timestamp (10) or higher, the HH part of the timestamp column will be displayed,
Therefore, using hour () on a shorter timestamp value produces an unpredictable result.
4. the invalid timestamp value is converted to the "zero" value (00000000000000) of the appropriate type ). (Datetime, date, and so on)

You can use the following statement for verification:
Create Table Test ('id' int (3) unsigned auto_increment, 'date1' timestamp (8) primary key ('id '));
Insert into Test Set ID = 1;
Select * from test;
+ ---- + ---------------- +
| ID | date1 |
+ ---- + ---------------- +
| 1 | 20021114 |
+ ---- + ---------------- +
Alter table test change 'date1' date1 'timestamp (14 );
Select * from test;
+ ---- + ---------------- +
| ID | date1 |
+ ---- + ---------------- +
| 1 | 20021114093723 |
+ ---- + ---------------- +

 

You can use the timestamp column type to automatically mark the insert or update operation with the current date and time.
If you have multiple timestamp columns, only the first one is automatically updated.
The first timestamp column is automatically updated under any of the following conditions:

1. The column value is not explicitly specified in an insert or load data infile statement.
2. The column value is not explicitly specified in an update statement and other columns change the value.
(Pay attention to setting an Update column as its existing value,
This will not cause the timestamp column to be updated,
If you set a column as its current value, MySQL ignores the changes for efficiency .)
3. You explicitly set the timestamp column as null.
4. The timestamp column except the first one can also be set to the current date and time, as long as the column is set to null, or now ().

Create Table Test (
'Id' int (3) unsigned auto_increment,
'Date1' timestamp (14 ),
'Date2' timestamp (14 ),
Primary Key ('id ')
);

Insert into test (ID, date1, date2) values (1, null, null );
Insert into Test Set ID = 2;
+ ---- + ---------------- +
| ID | date1 | date2 |
+ ---- + ---------------- +
| 1 | 20021114093723 | 20021114093723 |
| 2 | 20021114093724 | 00000000000000 |
+ ---- + ---------------- +
-> The value of date1 and date2 is the current time because date1 and date2 are set to null.
The second command does not set the values of the date1 and date2 columns. The first timestamp column date1 is updated to the current time,
The two timestamp columns, date2, change to "00000000000000" because the date is invalid"

Update Test Set ID = 3 where id = 1;
+ ---- + ---------------- +

Demo:

 

You can set the default value for the timestamp type in MySQL, just like other types.

1. Automatic update and insert to current time:

Table:

/* DDL information for-test. T1 */

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

Table CREATE TABLE

------ Success -------------------------------------------------------------------------------------------

T1 create table 't1 '(

'P _ C' int (11) not null,

'P _ time' timestamp not null default current_timestamp on update current_timestamp

) Engine = InnoDB default charset = gb2312

Data:

1 2007-10-08 11:53:35

2 2007-10-08 11:54:00

Insert into T1 (P_c) Select 3;

Update T1 set P_c = 2 where P_c = 2;

Data:

1 2007-10-08 11:53:35

2 2007-10-08 12:00:37

3 2007-10-08 12:00:37

2. Automatic insert to the current time, but not automatic update.

Table:

/* DDL information for-test. T1 */

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

Table CREATE TABLE

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

T1 create table 't1 '(

'P _ C' int (11) not null,

'P _ time' timestamp not null default current_timestamp

) Engine = InnoDB default charset = gb2312

Data:

Insert into T1 (P_c) Select 4;

Update T1 set P_c = 3 where P_c = 3;

1 2007-10-08 11:53:35

2 2007-10-08 12:00:37

3 2007-10-08 12:00:37

4 2007-10-08 12:05:19

3. A table cannot have two fields. The default value is the current time. Otherwise, an error occurs. But others can.

Table:

/* DDL information for-test. T1 */

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

Table CREATE TABLE

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

T1 create table 't1 '(

'P _ C' int (11) not null,

'P _ time' timestamp not null default current_timestamp,

'P _ timew2 'timestamp not null default '2017-00-00 00:00:00'

) Engine = InnoDB default charset = gb2312

Data:

1 2007-10-08 11:53:35 0000-00-00 00:00:00

2 12:00:37 0000-00-00 00:00:00

3 2007-10-08 12:00:37 0000-00-00 00:00:00

4 12:05:19 0000-00-00 00:00:00