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 m509ae0a398b0a916ef5e0eaa727f726f255),

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 (na4fd119eab8ec6b55a353bd39aa2f042255), 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

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 Mf33f5230141b8442092aebe8d9ff99ec=). 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.

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 Dd6221be6807307e368950a037a33a29c24, 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.

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!