What are the data types of mysql?

Mysql data types include: BOOL, TINY INT, INT, BIG INT, FLOAT, DOUBLE, DECIMAL, CHAR, VARCHAR, TINY TEXT, TEXT, Date, DateTime, TimeStamp, Year, etc.

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 type	Meaning (signed)
tinyint(m)	1 byte range (-128~127)
smallint(m)	2 bytes range (-32768~32767)
mediumint(m)	3 bytes range ( -8388608~8388607)
int(m)	4 bytes range (-2147483648~2147483647)
bigint(m)	8 bytes range (-9.22*10 to the 18th power)

Value range If unsigned is added, the maximum The value is doubled. For example, the value range of tinyint unsigned is (0~256).

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 typeMeaningfloat(m,d)Single precision floating point type 8-bit precision (4 bytes) m total number, d decimal placesdouble(m,d)Double precision floating point type 16-bit precision (8 bytes) m total number, d decimal placesSuppose a field is defined as float(6,3). If a number 123.45678 is inserted, 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 maximum integer part is 3 digits. If you insert the number 12.123456, the stored number is 12.1234. If you insert 12.12, the stored number is 12.1200.

3, fixed point number

Floating point Types store approximate values ​​in the database, 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 typechar(n)varchar(n)tinytexttextmediumtextlongtext

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 ways. _TEXT is stored in text mode. English storage is case-sensitive, while _Blob It is stored in binary format, regardless of case.

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

Meaning
Fixed length, up to 255 characters
Fixed Length, up to 65535 characters
Variable length, up to 255 characters
Variable length, up to 65535 characters
Variable length, up to 2 to the 24th power - 1 character
Variable length, up to 2 to the 32nd power - 1 character

MySQL data type	Meaning
date	Date'2008-12-2'
time	Time'12:25:36'
datetime	Date time '2008-12-2 22:06:44'
timestamp	Automatic 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 fields of this data type can store this The time when the record was last modified.

Data type attributes

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

2. The length and range of MYSQL data types

List of each data type and byte length:

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

##Year1 is displayed in the format of YYYY. For example: 2009Char(M)MVarChar(M)M Variable length string, requires M<=255Binary(M)MChar-like binary storage, characterized by inserting fixed-length shortfalls and padding 0VarBinary(M )MVariable length binary storage similar to VarChar, characterized by fixed length without padding 0Tiny TextMax :255Case insensitiveTextMax:64KCase insensitiveMedium TextMax:16MCase insensitiveLong TextMax:4G Case insensitive##TinyBlobBlobMediumBlobLongBlobEnumSetGeometryPoint##LineString##MultiPointMultiLineString

Data type

Byte length

Range or usage

Bit

1

Unsigned [0,255], signed [-128,127] , Tianyuan Blog Note: BIT and BOOL Boolean types both occupy 1 byte

TinyInt

1

Integer [0,255]

SmallInt

2

Unsigned [0,65535], signed [-32768,32767]

MediumInt

3

Unsigned[0,2^24-1], signed[-2^23,2^23-1]]

Int

4

Unsigned [0,2^32-1], signed [-2^31,2^31-1]

BigInt

8

Unsigned [0,2^64-1], signed [-2^63 ,2^63 -1]

Float(M,D)

4

Single 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 2

Unpacked 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.

Date

3

is displayed in the format of YYYY-MM-DD, for example: 2009-07-19

Date Time

8

is displayed in the format of YYYY-MM-DD HH:MM:SS, for example: 2009-07-19 11:22:30

TimeStamp

4

is displayed in the format of YYYY-MM-DD, for example: 2009-07-19

Time

3

is displayed in the format of HH:MM:SS. For example: 11:22:30

fixed-length string.

Max:255

Case sensitive

Max:64K

Case sensitive

Max:16M

Case Sensitive

Max:4G

Case Sensitive

1 or 2

Up to 65535 different enumeration values ​​

Up to 8

Up to 64 different values

##Polygon

##MultiPolygon

GeometryCollection

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:

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 characters Section
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

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 value that exceeds the column length will not be saved , and an error will occur.

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 examples illustrate this 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  +          | ab+            |
+----------------+----------------+
1 row in set (0.00 sec)

text and blob

When using text and blob field types, please pay attention to the following points in order to better utilize the performance of the database.

①BLOB and TEXT values ​​will also cause some problems of their own, especially when a large number of delete or update operations are performed. Deleting this kind of value will leave a large "hole" in the data table. Records that fill these "holes" in the future may have different lengths. In order to improve performance, it is recommended to regularly use the OPTIMIZE TABLE function to defragment such tables.

②Use synthetic (synthetic) index. Synthetic index columns are useful sometimes. One way is to create a hash value based on the contents of other columns and store this value in a separate data column. You can then find the row of data by retrieving the hash value. However, we should note that this technique can only be used for exact match queries (hash values ​​are not useful for range search operators like < or >=). We can use the MD5() function to generate the hash value, we can use SHA1() or CRC32(), or we can use our own application logic to calculate the hash value. Remember that numeric hash values ​​can be stored very efficiently. Likewise, if a hashing algorithm produces strings with trailing spaces, do not store them in CHAR or VARCHAR columns; they will be affected by trailing space removal.

Synthetic hash indexes are particularly useful for those BLOB or TEXT data columns. A search using a hashed identifier value is much faster than searching the BLOB column itself.

③Avoid retrieving large BLOB or TEXT values ​​when unnecessary. For example, a SELECT * query is not a good idea unless you can be sure that the WHERE clause as a constraint will only find the required rows of data. Otherwise, you could transmit a large number of values ​​over the network without any purpose. This is also an example where it helps us to have BLOB or TEXT identifier information stored in a synthetic index column. You can search the index column to determine which data rows are needed, and then retrieve the BLOB or TEXT value from the qualifying data rows.

④ Separate BLOB or TEXT columns into separate tables. In some environments, it may make sense to move these data columns to a second data table if it allows you to convert the data columns in the original data table to a fixed-length row format. This reduces fragmentation in the main table and gives you the performance benefits of fixed-length data rows. It also allows you to run SELECT * queries on the main data table without transmitting large amounts of BLOB or TEXT values ​​over the network.

Floating point numbers and fixed point numbers

In order to attract everyone's attention, let everyone see an example before introducing floating point numbers and fixed point numbers:

mysql> CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (0.29 sec)
mysql> insert into test values(131072.32,131072.32);
Query OK, 1 row affected (0.07 sec)
mysql> select * from test;
+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
1 row in set (0.00 sec)

From the above example We see that the value of column c1 has changed from 131072.32 to 131072.31. This is caused by the inaccuracy of floating point numbers.

In mysql, float and double (or real) are floating-point numbers, and decimal (or numberic) is a fixed-point number.

The advantage of floating-point numbers over fixed-point numbers is that when the length is certain, floating-point numbers can represent a larger data range; its disadvantage is that it can cause accuracy problems. In the future applications of floating-point numbers and fixed-point numbers, everyone should remember the following points:

Floating point numbers have error problems;
1. is sensitive to precision in currencies, etc. The data should be represented or stored with fixed-point numbers;
2. In programming, if floating-point numbers are used, special attention should be paid to error issues and try to avoid floating-point comparisons;
3. Be careful with floating-point numbers Handling of some special values.

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