MySQL data type

Source: Internet
Author: User

1. Integral type

MySQL data type Meaning (Signed)
tinyint (m) 1 byte range ( -128~127)
smallint (m) 2 byte range ( -32768~32767)
Mediumint (M) 3 byte range ( -8388608~8388607)
Int (m) 4 byte range ( -2147483648~2147483647)
BigInt (M) 8 byte Range (18 +-9.22*10 of the X-square)

If unsigned is added to the value range, the maximum value is doubled, such as the tinyint unsigned value range (0~256).
m in int (m) is the display width in the result set of the select query, does not affect the actual range of values, does not affect the width of the display, do not know what the use of this m.

2. Float type (float and double)

MySQL data type Meaning
Float (m,d) Single-precision floating-point 8-bit precision (4 bytes) m total number, D decimal place
Double (m,d) Double-precision floating-point 16-bit precision (8 bytes) m total number, D decimal place

Set a field defined as float (5,3), if you insert a number 123.45678, the actual database is 123.457, but the total number is actually the same, that is, 6 bits.

3. Fixed-point number

Floating-point types hold approximate values in the database, while the fixed-point type stores the exact values in the database.
The decimal (m,d) parameter m<65 is the total number, d<30 and d<m is the decimal place.

4. String (Char,varchar,_text)

MySQL data type Meaning
CHAR (n) Fixed length, up to 255 characters
VARCHAR (n) Fixed length, up to 65,535 characters
Tinytext Variable length, up to 255 characters
Text Variable length, up to 65,535 characters
Mediumtext Variable length, up to 2 of 24 square-1 characters
Longtext Variable length, up to 2 of 32 square-1 characters

Char and varchar:
1.char (n) If the number of characters in the deposit is less than n, then the space is appended to it, and then the space is removed when queried. So the char type stores a string with no spaces at the end, and varchar is not limited to this.
2.char (n) fixed length, char (4) regardless of whether it is deposited in several characters, will occupy 4 bytes, varchar is the actual number of characters stored in + 1 bytes (n<=255) or 2 bytes (n>255), so varchar (4), A deposit of 3 characters will take up 4 bytes.
The string retrieval speed of the 3.char type is faster than the varchar type.

varchar and text:
1.varchar can specify that the N,text cannot be specified, the internal storage varchar is the actual number of characters stored in + 1 bytes (n<=255) or 2 bytes (n>255), the text is the actual number of characters + 2 bytes.
The 2.text type cannot have a default value.
3.varchar can create indexes directly, text creates indexes to specify the number of characters before. varchar queries are faster than text, and the index of text does not seem to work in the case of all indexes being created.

5. Binary data (_BLOB)

1._blob and _text are stored in different ways, _text stored in text, English storage is case-sensitive, and _blob is stored in binary mode, regardless of case.
The data stored by 2._blob can only be read out in its entirety.
3._text can specify a character set, _blo does not specify a character set.

6. Date and Time type

MySQL data type Meaning
Date Date ' 2008-12-2 '
Time Time ' 12:25:36 '
Datetime Date Time ' 2008-12-2 22:06:44 '
Timestamp Automatically store record modification times

If you define a field that is timestamp, the time data in the fields is automatically refreshed when the other fields are modified, so the field of this data type can hold the last time the record was modified.

Properties of the data type

mysql keyword Meaning
Null Data columns can contain null values
Not NULL Data columns are not allowed to contain null values
DEFAULT Default value
PRIMARY KEY Primary key
Auto_increment Auto-increment for integer type
UNSIGNED No sign
CHARACTER SET Name Specify a character Set

Original Tears Yunshan Hai

MySQL data type (RPM)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.