What are the data types of mysql? Detailed explanation of mysql data types

This article lists the data type list (list) of mysql, which mainly includes five categories: integer type, floating point type, string type, date type and other data types. The following will provide a detailed explanation of these five data types. There are also lengths and ranges of mysql data types with usage recommendations and basic principles for selecting data types.

1. MySQL data types

Mainly include the following five categories:

Integer types: BIT, BOOL, TINY INT, SMALL INT, MEDIUM INT, INT, BIG INT

Floating point type: FLOAT, DOUBLE, DECIMAL

String type: CHAR, VARCHAR, TINY TEXT, TEXT, MEDIUM TEXT, LONGTEXT, TINY BLOB, BLOB, MEDIUM BLOB, LONG BLOB

Date type: Date, DateTime, TimeStamp, Time, Year

Other data types: BINARY, VARBINARY, ENUM, SET, Geometry, Point, MultiPoint, LineString, MultiLineString, Polygon, GeometryCollection, etc.

1. Integer type

##MySQL data typeMeaning (signed) tinyint(m)1 byte range (-128~127)smallint(m)2 byte range (- 32768~32767)mediumint(m)3 bytes Range (-8388608~8388607)int (m)4 bytes range (-2147483648~2147483647)##bigint (m)If unsigned is added to the value range, the maximum value will be doubled. For example, the value range of tinyint unsigned is (0~256).

8 bytes range (-9.22 *10 to the 18th power)

The m in int(m) represents the display width in the SELECT query result set. It does not affect the actual value range or the display width. I don’t know what the use of this m is.

2. Floating point type (float and double)

MySQL data typefloat(m,d)##double( m,d)Double precision floating point type 16-bit precision (8 bytes) m total number, d decimal placesSet a field definition It is float(6,3). If you insert a number 123.45678, the actual number stored in the database is 123.457, but the total number is subject to the actual number, that is, 6 digits. The integer part has a maximum length of 3 digits. If the number 12.123456 is inserted, 12.1234 is stored. If 12.12 is inserted, 12.1200 is stored.

Meaning
Single precision floating point type 8-bit precision (4 bytes) m total number, d decimal places

3. Fixed-point numbers

Floating point types are stored in the database is an approximate value, while fixed-point types store exact values ​​in the database.

decimal(m,d) The parameter m<65 is the total number, d<30 and d

4. String (char, varchar, _text)

MySQL data typeMeaningchar(n)Fixed length, up to 255 characters varchar(n)Fixed length, up to 65535 characters tinytextVariable length, up to 255 characterstextVariable length, Up to 65535 charactersmediumtextVariable length, up to 2 to the power of 1 characterlongtext Variable length, up to 2 to the 32nd power - 1 character char and varchar: 1.char(n ) If the number of characters stored is less than n, spaces will be added after them, and the spaces will be removed when querying. Therefore, there cannot be spaces at the end of strings stored in char type, and varchar is not limited to this.

2.char(n) fixed length, char(4) will occupy 4 bytes no matter how many characters are stored, varchar is 1 byte of the actual number of characters stored (n< =255) or 2 bytes (n>255),

So varchar(4), storing 3 characters will occupy 4 bytes.

3.Char type string retrieval speed is faster than varchar type.

varchar and text:

1.varchar can specify n, text cannot. The internal storage of varchar is the actual number of characters stored, 1 byte (n<=255) or 2 bytes ( n>255), text is the actual number of characters and is 2 bytes

.

2. The text type cannot have a default value.

3.varchar can directly create an index, and text needs to specify the first number of characters to create an index. The query speed of varchar is faster than that of text. When indexes are created, the index of text does not seem to work.

5. Binary data (_Blob)

1. _BLOB and _text are stored in different storage methods. _TEXT is stored in text mode, and English storage is case-sensitive, while _Blob is stored in binary mode. Not case sensitive.

2._The data stored in BLOB can only be read out as a whole.

3._TEXT can specify the character set, _BLO does not need to specify the character set.

6. Date and time type

MySQL data typeMeaningdateDate'2008-12-2'timeTime'12:25:36'datetimeDate time '2008-12-2 22:06:44'timestampAutomatic storage record modification time

If you define a field as timestamp, the time data in this field will be automatically refreshed when other fields are modified, so this data type field can store the last modified time of this record.

Data type attributes

##DEFAULT Default valuePRIMARY KEYPrimary keyAUTO_INCREMENTAuto increment , suitable for integer types UNSIGNEDUnsignedCHARACTER SET nameSpecifies a character Set

MySQL keyword	Meaning
NULL	Data columns can contain NULL values
NOT NULL	Data columns are not allowed to contain NULL values

2. The length and range of MYSQL data types

List of each data type and byte length:

Data typeByte lengthRange or usageBit1Unsigned [0,255], signed [-128,127], Tianyuan Blog Note: BIT and BOOL Boolean types both occupy 1 byte TinyInt1Integer[0,255]SmallInt2Unsigned [0,65535], signed [-32768,32767 ]MediumInt3Unsigned[0,2^24-1], signed[-2^23,2^23 -1]]Int4Unsigned[0,2^32-1], signed[-2^31, 2^31-1]BigInt8Unsigned [0,2^64-1], signed [-2^ 63 ,2^63 -1]Float(M,D)4Single precision floating point number. Tianyuan Blog reminds that D here is precision. If D<=24, it is the default FLOAT. If D>24, it will be automatically converted to DOUBLE type. Double(M,D)8 Double precision floating point. Decimal(M,D)M 1 or M 2Unpacked floating point number, usage is similar to FLOAT and DOUBLE, Tianyuan The blog reminds you that if the Decimal data type is used in ASP, the Decimal read directly from the database may need to be converted into a Float or Double type before operation. Date3 is displayed in the format of YYYY-MM-DD, for example: 2009-07-19Date Time8 is displayed in the format of YYYY-MM-DD HH:MM:SS, for example: 2009-07-19 11:22:30TimeStamp4 is displayed in the format of YYYY-MM-DD, for example: 2009-07-19Time3 is displayed in the format of HH:MM:SS. For example: 11:22:30##YearChar(M)fixed-length string. VarChar(M)Binary(M)VarBinary(M )Tiny TextTextMedium TextLong Text##TinyBlobMax:255Case sensitiveBlobMax:64KCase sensitiveMediumBlobMax:16M Case SensitiveLongBlobMax:4GCase SensitiveEnum1 or 2 Up to 65535 different enumeration values ​​Set Up to 8 Up to 64 different valuesGeometryPoint##Polygon##MultiPolygonGeometryCollection

3. Usage Suggestions

1. When specifying the data type, the principle of small size is generally adopted. For example, if you can use TINY INT, it is best not to use INT, and if you can use FLOAT type, it is best not to use DOUBLE type, so It will greatly improve the operating efficiency of MYSQL, especially under large data volume testing conditions.

2. There is no need to design the data table too complicated. The distinction between functional modules may be more convenient for later maintenance. Be careful when presenting a hodgepodge of data tables.

3. The classification of data tables and fields Naming is also a skill

4. Before designing the data table structure, please imagine it is your room. Maybe the result will be more reasonable and efficient

5. The final design result of the database must be It is a trade-off between efficiency and scalability. It is inappropriate to favor either side.

Basic principles for selecting data types

Prerequisite: Use a suitable storage engine.
Selection principle: Determine how to choose the appropriate data type based on the selected storage engine.
The following selection methods are classified by storage engine:

• MyISAM data storage engine and data columns: MyISAM data table, it is best to use fixed-length (CHAR) data columns instead of variable Data column of length (VARCHAR).

• MEMORY storage engine and data columns: MEMORY data tables currently use fixed-length data row storage, so it does not matter whether you use CHAR or VARCHAR columns. Both are handled as CHAR types.

• InnoDB storage engine and data columns: It is recommended to use VARCHAR type.

For InnoDB data tables, the internal row storage format does not distinguish between fixed-length and variable-length columns (all data rows use head pointers pointing to data column values), so in essence , using fixed-length CHAR columns is not necessarily simpler than using variable-length VARCHAR columns. Therefore, the main performance factor is the total storage used by the data rows. Since CHAR takes up more space on average than VARCHAR, it is better to use VARCHAR to minimize the total storage and disk I/O of data rows that need to be processed.
Let’s talk about fixed-length data columns and variable-length data columns.

char is similar to varchar

The CHAR and VARCHAR types are similar, but they are saved and retrieved differently. They also differ in terms of their maximum length and whether trailing spaces are preserved. No case conversion is performed during storage or retrieval.
The following table shows the results of saving various string values ​​into CHAR(4) and VARCHAR(4) columns, illustrating the difference between CHAR and VARCHAR:

1	is displayed in the format of YYYY. For example: 2009
M
M	Variable length string, requires M<=255
M	Binary storage similar to Char, characterized by inserting fixed length and filling 0
M	Variable length binary storage similar to VarChar, characterized by fixed length without padding 0
Max :255	Case insensitive
Max:64K	Case insensitive
Max:16M	Case insensitive
Max:4G	Case insensitive
		##LineString
	##MultiPoint
	MultiLineString

Value	CHAR(4)	Storage requirements	VARCHAR(4)	Storage requirements
''	' '	4 bytes	''	1 byte
'ab'	'ab '	4 bytes	'ab '	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 characters Section

Please note that the value in the last row in the above table only applies when strict mode is not used; if MySQL is running in strict mode, the column length will not be exceeded. The value is not saved , and an error occurs.
The values ​​retrieved from CHAR(4) and VARCHAR(4) columns are not always the same because trailing spaces are removed from the CHAR column when retrieving. The following example illustrates the difference:
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO vc VALUES ('ab ', 'ab ');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT CONCAT(v, ' '), CONCAT(c, ' ') FROM vc;
--------------------------------
| CONCAT(v, ' ') | CONCAT(c, ' ') |
--------------------------------
| ab                                        ---------------- ----------------
1 row in set (0.00 sec)
Related Article:

Detailed explanation of MYSQL data types

Detailed explanation of the correct use of numeric types of MySQL data types

Related videos:

Database mysql video tutorial

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