Data types supported by MySQL

Numeric type

Integer type	Bytes	Minimum value	Maximum Value
TINYINT	1
SMALLINT	2
Mediumint	3
INT, INTEGER	4
BIGINT	5
Floating-point type
FLOAT	4
DOUBLE	8
Fixed-point number types
DEC (M, D)	M + 2	The maximum value range is the same as double, and the range of valid values for a given decimal is determined by M and D
DECIMAL (M, D)
Bit type
BIT (M)	1 ~ 8	BIT (1)	BIT (64)

P53) for integer data, MySQL also supports specifying a bit width within the parentheses following the type name, such as INT (5), which fills the width before the number when the value is less than 5 bits, and the default bit int (11) If the specified width is not displayed. Commonly used in conjunction with Zerofill . Zerofill is the meaning of "0" padding, that is, the space in the number of digits is not enough to fill with the character "0".

The bit width has no effect on the size of the inserted number, it does not truncate the part that exceeds the bit width, and it still holds the actual precision of the int type, when the width format has no meaning, and no "0" character is populated on the left.

If a column is specified as Zerofill, MySQL automatically adds the unsigned property to the column.

P54)auto_increment

When inserting null into a auto_increment column, MySQL inserts a value that is 1 larger than the current maximum value. there can be at most one auto_increment column in a table . For any column that wants to use auto_increment, it should be defined as not NULL and defined as primary key or defined as a unique key.

In the case of decimals, MySQL is divided into two ways: floating point and fixed point number. Floating-point numbers include float (single-precision) and double (double-precision), while fixed-point numbers are represented by only a decimal. Fixed-point numbers are stored as strings inside MySQL, more accurate than floating-point numbers, and are suitable for representing high-precision data such as currencies.

Floating-point and fixed-point numbers can be represented by the type name plus "(m,d)", whichmeans that the value shows a total of M-bit numbers (integer digits + decimal places), where the D-bit is after the decimal point, and M and D are also known as precision and scale. the use of floating-point numbers followed by "(M,d)" is a non-standard usage and should not be used if you are migrating to a database. decimal When you do not specify a precision, the default integer bit is 10, and the default decimal place is 0.

When the operation appears warning reminder, you can pass show warnings; To show what's going on.

For bit (bit) types, the bits (m) can be used to hold many binary numbers, M range from 1~64, and default to 1 bits if not written . For bit fields, direct use of the SELECT command will not see the result, and can be read in bin (column name) (shown in binary format) or hex (column name) (displayed as a 16-in) function.

Date Time Type

• Month and day, usually expressed in date.
• Month and day, and seconds, usually expressed in DateTime.
• Time division seconds, usually expressed in time.

Date and Time type	Bytes	Minimum value	Maximum Value
DATE	4	1000-01-01	9999-12-31
Datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	4	19700101080001	At some point in 2038.
Time	3	-838:59:59	838:59:59
Year	1	1901	2155

If you need to insert frequently or update the date to the current system time, you typically use timestamp to indicate that the timestamp value returns a string that is displayed as "Yyyy-mm-dd HH:MM:SS", with a fixed width of 19 characters, and if you want to get a numeric value, You should add "+0" to the timestamp column.

Beyond the valid range of date values, the system will prompt for errors under the default Sqlmode and will be stored with a value of 0.

The now () function returns the current system time.

MySQL sets the default value to the system date only for the first timestamp field in the table, and if there is a second timestamp type, the default value is set to 0 value. You can set the timestamp default value for other columns to a different constant date, but you cannot modify it to Current_timestamp because MySQL specifies that a field of timestamp type can have only one column of the default value of Current_timestamp.

timestamp also has an important feature that is related to time zones. when the date is inserted, it is converted to the local time zone, and when removed from the database, it is also necessary to convert the date to the local time zone. In this way, users in two different time zones may see the same date differently.

Show variables like ' Time_zone '; View current time zone

set time_zone= ' +9:00 '; Modify the time zone to East Zone nine

The Timestampde value range is one day of 19,700,101,080,001-2038 years, so it is not suitable for storing older dates.

String Type  

The difference between char and varchar is that the length of the char column is fixed to the length declared when the table is created, the length can be any value from 0~255, and the value in the varchar column is a variable string length that can be specified as a value between 0~65535. When a string is stored, the Char column removes trailing spaces, while varchar preserves the trailing spaces of the string.

Data types supported by MySQL