MySQL 5.7 Common Data Types

——Notes from "MySQL in Simple Terms (Second Edition)"

Numeric Types

Integer Types

For integer types, MySQL supports specifying the display width by using (n) after the type name. For example, int(5) means that when the width of the value is less than 5 digits, the width is filled in front of the number. If the specified width is not displayed, the default is int(11) . It is usually used with zerofill, which means filling with '0'.

Example:

--Create table t1 with two fields, id1 and id2, and specify the value widths as int and int(5) respectively
mysql> create table t1 (id1 int,id2 int(5));
mysql> desc t1; 

--Insert the value 1 into both id1 and id2
mysql> insert into t1 values ​​(1,1);
mysql> select * from t1; 

--Add zerofill parameters to the id1 and id2 fields respectivelymysql> alter table t1 modify id1 int zerofill;
mysql> alter table t1 modify id2 int(5) zerofill; 

After setting the width limit, if you insert a value larger than the width limit, it will not have any impact on the inserted data, and the data will still be saved according to the type's book precision. At this point, the width format is actually meaningless and no "0" characters will be filled on the left.

--Insert 1 into id1 and 12345678 into id2 of table t1
mysql> insert into t1 values(1,12345678);
mysql> select * from t1; 

All integer types have an optional attribute UNSIGNED (unsigned). If you need to store non-negative numbers in the field or need a larger upper limit value, you can use this option. When a column is specified as zerofill, MySQL automatically adds the UNSIGNED attribute to the column.

In addition, the integer type also has a unique AUTO_INCREMENT attribute, which means that the value of this column is auto-incremental. AUTO_INCREMENT values ​​generally start at 1 and increase by 1 for each row. When you insert NULL into an AUTO_INCREMENT column, MySQL inserts a value that is one greater than the column's current maximum value. A table can have at most one AUTO_INCREMENT column.

Any column you want to use AUTO_INCREMENT with should be defined as NOT NULL and UNIQUE.

Floating-point types

For the representation of decimals, MySQL divides them into two types: floating point numbers and fixed point numbers. Floating-point numbers include float (single precision) and double (double precision), while fixed-point numbers only have decimal. Fixed-point numbers are stored in MySQL as strings. They are more precise than floating-point numbers and are suitable for representing high-precision data such as currency.

Both floating-point numbers and fixed-point numbers can be represented by adding "(M,D)" after the type name. "(M,D)" means that the value is displayed with a total of M digits and D decimal digits. When MySQL saves floating-point values, it rounds off excess digits. So if you insert 123.006 into float(5,3), the stored value is 123.01. When the precision of float and double is not specified, the default is determined by the actual hardware and operating system; when decimal is not specified, the default integer digits are 10 and the decimal digits are 0.

Example:

--Create table tf, set fields id1, id2, id3 to float(5,2), double(5,2), decimal(5,2) respectively
mysql> create table tf( id1 float(5,2),id2 double(5,2),id3 decimal(5,2)); 

--Insert data into the three fields in the table 1.23
mysql> insert into tf values(1.23,1.23,1.23); 

--Insert data 1234.005 into the table respectively
mysql> insert into tf values(1234.005,1234.005,1234.005); 

--Insert data 123.005 into the table respectively
mysql> insert into tf values(123.005,123.005,123.005); 

--Insert data 123.006, 123.006, 123.004 into the table respectively
mysql> insert into tf values(123.006,123.006,123.004); 

--Remove the precision of the table tf field and reinsert 1.23
mysql> alter table tf modify id1 float;
mysql> alter table tf modify id2 double;
mysql> alter table tf modify id3 decimal;
mysql> insert into tf values(1.23,1.23,1.23);
mysql> desc tf; 

--Insert 1.234567123321,1.234567123321123321,1.23234233 into the table
mysql> insert into tf values(1.234567123321,1.234567123321123321,1.23234233); 

Bit Type

For the BIT type, it is used to store bit field values. BIT(M) can be used to store multi-bit binary numbers. The range of M is from 1 to 64. If not written, the default is 1 bit. If you use the SELECT command directly, you will not see the query results. You need to use bin() (display in binary) function or hex() (display in hexadecimal) function to read them.

Example:

--Create table tb, define field id1 as bit(1), id2 as bit
mysql> create table tb(id1 bit(1),id2 bit); 

--Insert 1,1 into tb
mysql> insert into tb values(1,1);
mysql> select * from tb;
mysql> select bin(id1),hex(id2) from tb; 

When data is inserted into a bit field, it is first converted to binary. If the number of bits allows, the data is successfully inserted. If the number of bits exceeds the defined number of bits, the data is not inserted.

Time Type

There are many data types in MySQL that can be used to represent date and time. The following table lists all the date and time types supported in MySQL 5.0.