Shulou(Shulou.com)06/01 Report--

This article is about what data types are available in mysql. Xiao Bian thinks it is quite practical, so share it for everyone to make a reference. Let's follow the editor and have a look.

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

MySQL data types

It mainly includes the following five categories:

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

Floating-point number types: FLOAT, DOUBLE, DECIMAL

String types: 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

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 (+-9.22 ° 10 to the 18th power)

If unsigned is added, the maximum value is doubled, for example, tinyint unsigned has a value range of (0~256).

int(m) in m is the display width of SELECT query result set, does not affect the actual value range, does not affect the display width, do not know what this m is used for.

Floating-point types (float and double)

MySQL data type meaning float(m,d) single-precision floating-point type 8-bit precision (4 bytes) m total number, d decimal places double(m,d) double-precision floating-point type 16-bit precision (8 bytes) m total number, d decimal places

Let a field be defined as float(6,3). If you insert a number 123.45678, the actual database stores 123.457, but the total number is subject to reality, that is, 6 digits. The integer part has a maximum of 3 bits. If the number of insertions is 12.123456, it is stored as 12.1234. If the number of insertions is 12.12, it is stored as 12.1200.

3. Number of fixed points

Floating-point types store approximate values in the database, while fixed-point types store exact values in the database.

decimal(m,d) arguments m 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 changes from 131072.32 to 131072.31, which is caused by the imprecision of floating point numbers.

In mysql float, double (or real) are floating-point numbers, and decimal (or numeric) are fixed-point numbers.

The advantage of floating-point numbers over fixed-point numbers is that they can represent a larger range of data for a given length; the disadvantage is that they cause precision problems. In future applications of floating-point numbers and fixed-point numbers, remember the following:

Floating-point numbers have error problems; data sensitive to precision such as currency should be expressed or stored with fixed-point numbers; in programming, if floating-point numbers are used, special attention should be paid to error problems, and floating-point comparison should be avoided as much as possible; attention should be paid to the handling of some special values in floating-point numbers. Thank you for reading! What data types are shared here about mysql, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge. If you think the article is good, you can share it so that more people can see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.