MySQL: Syntax: data type, text constant

Number Type: allows you to set the display width of integers. This setting does not affect the actual range of values. For bit, floating point, and fixed point decimal places, M indicates the number of valid digits, the range of impact values.

• Serial: bigint unsigned not null auto_increment unique
• Serial default value: not null auto_increment unique
• Bit [(M: = 1: 64)] m is 1 by default
• Tinyint [(m)] [unsigned] [zerofill]
• {Bool | Boolean}: tinyint [1] -- in fact, true = 1 false = 0. During Storage, all values other than 0 are true (1)
• Smallint [(m)] [unsigned] [zerofill]
• Mediumint [(m)] [unsigned] [zerofill]
• {Int | integer} [(m)] [unsigned] [zerofill]
• Bigint [(m)] [unsigned] [zerofill]
• Float [(m, D)] [unsigned] [zerofill]: -- The default depends on hardware.
• Float [(p)]: -- type for ODBC
• {Double [precision]} [(m, D)] [unsigned] [zerofill]: -- hardware-dependent by default
• {Dec | decimal | numeric | fixed} [(M: = 10: 65, D: 0: 30)] [unsigned] [zerofill] m is 10 by default, and D is 0 by default.

 

Date and Time Type

• Date: '2017-01-01 ': '2017-12-31' display: 'yyyy-MM-DD 'input: {text constant | number}
• Datetime: '2017-01-01 00:00:00 ': '2017-12-31 23:59:59' display: 'yyyy-MM-DD hh: mm: ss' input: {text constant | number}

• Timestamp: '2017-01-01 00:00:01 'UTC: '2017-01-09 03:14:07' UTC: 'yyyy-MM-DD hh: mm: ss' input: {text constant | number}
  • The value stored in timestamp is the number of seconds since '2017-01-01 00:00:00 ', but it cannot represent '2017-01-01 00:00:00 ', because its value is 0, there are other meanings indicating '2017-00-00 00:00:00'
  • The main function of timestamp is to automatically update the insert or update time of an existing record: the first timestamp column in the table will be automatically updated when these two operations occur, or when the value is null. Of course, you can also display other values to him!
  • Generally, the timestamp column is always a 19-Bit String. You can use "+ 0" to implicitly convert it to a bigint value.

• Time '-838: 59: 59': '838: 59: 59' display: 'hh: mm: ss' input: {text constant | number}
• Year [(4 | 2)]: '000000': '000000' the default value is 4-bit length, and the range of 2-bit length is '2017: 1901'

 

Text Type

1. Generally, text types have the following general features:
2. The length of the text type is a character length, not a byte length.
3. The text type includes the [character set | charset] character set attribute, [collate] sorting rule attribute, and [binary] attribute. This attribute changes the Data Type of the text type.
4. ASCII is the abbreviation of Character Set Latin1
5. Unicode is the abbreviation of Character Set ucs2
6. Binary is the binary sorting rule of the specified character set. This will sort the strings and compare them based on the numeric values of the character set.

• [National] {character | Char} [(M: 1: 255)] [Character Set cs_name] [collate c_name] -- MySQL allows char of M = 0, only compatible with some applicationsProgramThe value can only be null or ''. Whether to delete trailing spaces of char is controlled by pad_char_to_full_length.
• Nchar is short for national Char. It uses the utf8 character set.
• [National] {character varying | varchar} [(M: 1: 65535)] [Character Set cs_name] [collate c_name] -- its maximum length is affected by the row size, other columns, and character set. Each varchar column occupies an additional 1-2 bytes.
• Nvchar is short for national varchar. It uses the utf8 character set.
• Binary (m) -- similar to Char, But it stores binary strings in bytes.
• Varbinary (m) -- similar to Char, But it stores binary strings in bytes.
• Tinyblob -- the maximum storage capacity is 2 ^ 8-1 bytes, and the actual length of the value of the other 1 bytes is stored.
• Tinytext [Character Set cs_name] [collate c_name] -- the maximum storage space is 2 ^ 8-1 char, and the actual length of the value of the other 1 byte is stored.
• Blob -- stores up to 2 ^ 16-1 bytes, and the actual length of the other 2 bytes
• Text [Character Set cs_name] [collate c_name] -- the maximum storage space is 2 ^ 16-1 Char. the actual length of the values of the other two bytes is stored.
• Mediumblob -- stores up to 2 ^ 24-1 bytes, and stores the actual length of the other 3 bytes.
• Mediumtext [Character Set cs_name] [collate c_name] -- the maximum storage space is 2 ^ 24-1 Char. the actual length of the values of the other four bytes is stored.
• Longblob -- maximum storage of 2 ^ 32-1 bytes. the actual length of the value of the other 3 bytes is stored.
• Longtext [Character Set cs_name] [collate c_name] -- the maximum storage space is 2 ^ 32-1 char, and the actual length of the remaining 4 bytes is stored.
• Enum ('v1 ', 'v2 ',.....) [Character Set cs_name] [collate c_name] -- a maximum of 65535 different values. an Enum String object can have only one value in {list | null |, its internal integer Representation
• Set ('v1 ', 'v2 ',.....) [Character Set cs_name] [collate c_name] -- a maximum of 64 different values. an Enum String object can only have any value in the list, and its internal integer Representation

 

Default Value of Data Type

• You can use the default keyword to specify the default value for a column.
• The default nasal value must be a constant, with the exception of the timestamp column: You can specify current_timestamp
• If the column can be null, the specified default null can be displayed.

• If the column cannot be null, it is in insert, replace, update, and other operations. Check whether strict SQL mode is enabled for MySQL. Errors may occur in strict mode; in non-strict mode:

1. The number is 0. When the number type is auto_increment, it is the next value.
2. Timestamp is the current date and time.
3. Other Date and Time types are appropriate "zero" Values
4. The enum type is the first value.
5. Other character types are empty strings''

 

 

 

 

Character constant: [_ Character Set Name] 'character sequence... '[sorting rule name] -- a c-style string that can recognize "\" escape characters

• Double quotation marks indicate the style, which is not recommended.
• Escape characters are case sensitive. Escape will not be performed if it is not an escape character.
• N' character sequences' = _ utf8' character sequences'

 

Numeric constant: Do you want to use it again !!

 

Hexadecimal literal constant:

• {X | x} '{[0-9a-fa-f]} *' -- standard SQL Style
• 0x {[0-9a-fa-f]} * -- ODBC standard style
• Note: hexadecimal character constants are converted to bigint type in the numeric environment, and to character strings in the text environment.

 

Boolean: true or false-case-insensitive

 

Binary literal constant:

• B '{[01]} *' -- standard SQL Style
• 0b {[01]} * -- ODBC Style
• Note: Binary literal constants are always binary strings. to use them as numbers, you must use an explicit cast

 

Null: unknown or no data, not equal to 0 or null string or ascii nul (ASCII value 0)

• Case Insensitive
• Note that "\ n" can be used to indicate

 

 

Related Knowledge:

• Ansi_quotes settings
• Character Set
• Sorting rules
• Hex (), Bin (), OCT (), cast () Functions