What are the mysql data types?

mysql data types include: 1. Numeric type; 2. Date and time type; 3. String types are CHAR, VARCHAR, BINARY [binary data type], BLOB, TEXT [text type], ENUM [ Enumeration type] and SET [data collection].

mysql data types are:

The data types supported by MySQL are in several categories: Numeric types, date and time types and string (character) types.

Data type:

Note:

• We often use num int when defining fields (10) In this way of writing unsigned, the int(10) does not mean that the field will store 10-length numbers.

• int(M) represents the format of number display, and is only valid when the field attribute is set to zerofill.

eg：

Tinyint(4) -- The actual stored value is 2. If zerofill is not specified, the result of the query is 2. If zerofill is specified, then The query result is 0002, and 0 is used to fill the left side;

zerofill attribute:

• If the field specifies the zerofill attribute, mysql will automatically Field plus UNSIGNED attribute.

• The ZEROFILL attribute is ignored when involved in column expressions or UNION queries

Floating point:

Float(M,D): M is the number of digits in the number, including the integer part and decimal part; D is The decimal part ,[D If equal, the integer part is 0, and the maximum value is 0.99

　　eg：

• Float(2,1) -- represents one integer and one decimal, eg:1.1

• ## Float(1,2) --

  Syntax error, only 1 digits How can there be 2 decimal places

## About the default value:

The default value must be a constant light, not a function or expression. Exception: The default value of
• TIMESTAMP

  can be used CURRENT_TIMESTAMP

  ##BLOB and TEXT
Cannot set default value

• If a column does not clearly indicate the default value:

If the database is

strict mode

• mode, an error will be reported

  If the mode is not in
strict mode

• mode, the system will confirm the use according to the type What default value

Date and time format:

The date and time type representing time value is datetime (DATATIME) , date (DATA), timestamp (TIMESTAMP), time (TIME) and year (YEAR).

DATATIME range: '1000-01-01 00:00:00'

to

'9999-12-31 23 :59:59'. DATA range: '1000-01-01'

to

'9999-12-31' TIMESTAMP range: '1970-01-01 00:00:01'

UTC to

'2038-01-19 03:14:07' TIME range ：'-838:59:59'

to

'838:59:59'

YEAR range: year(4) --- 1901~2155; year(2) --- 00~99

TIMESTAMP: The timestamp column is displayed in the same format as the DATETIME column. In other words, the display width is fixed to 19 characters and the format is 'YYYY-MM-DD HH:MM:SS'.

The TIMESTAMP data type provides automatic initialization and update. But there can only be one column with default value and update specified as CURRENT_TIMESTAMP

　1. Automatic initialization and automatic update eg: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

　2. Only automatic initialization; eg: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

　3. Only automatic update; eg: ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

　4. None; eg: ts TIMESTAMP DEFAULT 0

String type :

The string types are CHAR, VARCHAR, BINARY [binary data type], BLOB, TEXT [text type], ENUM [enumeration type] and SET [data collection].

The difference between char and varchar: the difference in storage length lies in the maximum length and whether to retain spaces;

Char will retain spaces, varchar will not retain spaces

char is 0~255; varchar is 0~65535

　

##　ENUM: An enumeration is a string object that selects a value from a list that explicitly enumerates the allowed values ​​for the column specification when creating the table. [When the inserted value cannot match the value of the list, the default is the maximum index value]

 , ,

numbers ENUM('0','1','2')

mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');mysql> SELECT * FROM t;+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+

　SET: A set is an object that can have zero or more value strings, each of which must be selected from a list to allow the value specified when creating the table. [When the inserted value cannot match the value of the list, MYSQL will ignore the value that does not exist in the list]

1 CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));2 3 INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

It can be seen from the definition of ENUM The difference from SET is whether the value is one or more of the values ​​specified when defining. There is also a different behavior when the inserted value does not exist in the list.

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of What are the mysql data types?. For more information, please follow other related articles on the PHP Chinese website!