Length of various data types in MySQL and how to choose them in development

Contact MySQL This database about two years, but because there is no special in-depth system to learn, mostly also stay in the state of smattering. Today at work just met the problem of table design, by the way to write a blog, MySQL data type and field type selection in this area to figure out.

The data types in MySQL are broadly classified into three broad categories, numeric types, time date types, and string types. These three types are described in more detail below.

First, numeric type

MySQL supports all numeric types in standard SQL, including strict numeric types (INTEGER, SMALLINT, DECIMAL and numeric ) , as well as approximate numeric data types (FLOAT, REAL, and double PRECISION), and extended on a standard SQL basis adds TINYINT, Mediumint and bigint, these 3 different lengths of the entire type and the bit types that hold the bits data. In the daily table definition, the most common type of integer is still the data of int, and the floating-point type is dependent on the case. The range represented by each data type is shown in the following table:

Note: I have been involved in the development of IT projects in the telecommunications industry and banking, and when using the data they provide, you will always find the data type of decimal, which is mostly used in the database with decimal digits, rather than the double type that is commonly used in development and has the same maximum range of values. Double and decimal are floating point types, but decimal is twice times more accurate than double, four times times float, and decimal is a better choice than double for the industry where data requirements are very accurate in banks or communications. However, it is important to note that decimal is also a floating-point type of data, but the relative precision is higher, in actual use if the number of digits after the decimal point is the same as there will be a loss of precision.

Specifying a width for an integral type, such as int (one), is the same for storage, int (1) and int (20) , which does not limit the legal range of values, but specifies how many bits the MySQL interaction with the client should display, such as when you insert a 123456 value into int (1) , the database has actually been deposited in 123456, just for the client to find out is 1.

Ii. Time and date type

There are five types of time types available in MySQL, Date,datetime,timestamp,time and year, respectively. The following table is a detailed range of date types.

• If you want to use the date, it is usually represented by date.
• It is usually expressed in datetime if it is to be used to denote a month or a day.
• It is usually represented by time if it is used only to represent seconds and minutes.
• If you need to insert frequently or update the date to the current system time, you typically use timestamp to represent it.
• The TIMESTAMP value returns a string that appears in the format "Yyyy-mm-dd HH:MM:SS", with a fixed width of 19 characters. If you want to get a numeric value, you should add +0 in the timestamp column. If you are only representing the year, you can use years to indicate that it takes up less space than date. Year has a 2-bit or 4-bit format. The default is 4-bit format. In 4-bit format, the allowed values are 1901~2155 and 0000. In 2-bit format, the allowable value is 70~69, which is expressed from 1970-2,069. MySQL Displays the year value in yyyy format.

Three, String type

　　MySQL provides a variety of storage types for character data, and different versions may vary. In version 5.0, for example, MySQL includes a variety of data types, such as Char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and set. It is commonly used for varchar,text and so on. where char and varchar differ in the following

1. The length of the char is fixed, and the length of the VARCHAR2 is changeable, for example, storing the string "abc", for Char (10), indicating that the characters you store will account for 10 bytes (including 7 null characters), and the same


3. Currently varchar is synonymous with VARCHAR2. The industry standard varchar type can store an empty string, but Oracle does not, although it retains the right to do so later. Oracle has developed a data type VARCHAR2,
This type is not a standard varchar, it will change the attribute of the varchar column in the database to store the null value instead of the empty string. If you want to have backward compatibility capabilities, Oracle recommends using VARCHAR2 instead of varchar.

The following table lists all the character types in MySQL.

When the actual build table defines the field type, the string will typically consider using varchar, which is more efficient when the field data has clear precision and length. Select the text input when there is a long text input. Others are seldom used in normal development.

Length of various data types in MySQL and how to choose them in development