Data types for MySQL

Each constant, variable, and parameter has a data type, which is used to specify a certain storage format, constraint, and valid range. MySQL provides a variety of data types, primarily numeric, string type, date, and time types. The data types supported by different MySQL versions may be slightly different.

　　Numeric type:
MySQL supports all numeric types in standard SQL, including strict numeric types, approximate numeric data types, and, based on this, some extensions, adding three different lengths of integral types.

The "Out of range" error message occurs in integer types, with five types of tinyint, smallint, mediumint, int, and bigint, depending on the range of values and how they are stored. In order to avoid this kind of problem, when choosing the data type, we should determine its range according to the actual situation of the application, and finally choose the data type according to the determined result.

For integer data, MySQL also supports the ability to specify the display width of parentheses after the type name, such as INT (5), which fills the width before the number when the numeric width is less than 5 bits, and the default is int (11) If the specified width is not displayed. Generally with the use of Zerofill, Zerofill is the "0" fill the meaning, that is, the number of digits is not enough space with the character "0" filled.

All integer types have an optional attribute unsigned (unsigned), if you need to save a non-negative number in the field or need a large upper value, you can use this option, his value range is normally worth 0, the upper limit of the original value of twice times, for example, tinyint signed range is -128~+ 127, and the unsigned range is 0~255. If a column is specified as Zreofill, MySQL automatically adds the unsigned property to the column.

The integer type also has a property, Auto_increment. You can use this property when you need to produce a unique identifier or sequential value, which can be used only for integers. The atuo_increment value typically starts at 1 and increases by 1 per row. When you insert null into a auto_increment column, MySQL inserts a value that is 1 larger than the current maximum value in the column. There can be at most one auto_increment column in a table. For any column that wants to use auto_increment, it should be defined as not NULL and defined as primary key or a unique key.

The decimal type, MySQL is divided into two ways: floating-point and fixed-point number. Floating-point numbers include float (single-precision) and double (double-precision), while the number of points is only a decimal representation. Fixed-point numbers are stored as strings in MySQL and are more accurate than floating-point numbers, and are suitable for representing high-precision data such as currencies.

Float and fixed point numbers can be appended (m,d) to the type name (M,D) to indicate that the value shows a total of M-digits (integer digits + decimal places) where D is after the decimal point, and M and D are also known as precision and scale. It is important to note that the use of floating-point numbers followed by "(M,d)" is a non-standard usage and should not be used if you are migrating to a database. float and double When you do not specify precision, the default is displayed according to the actual precision (determined by the actual hardware and operating system), while decimal does not specify precision, the default integer bit is 10, and the default decimal bit is 0.

Floating-point number if not write precision and scale, it will be displayed according to the actual precision value, if there is precision and scale, it will automatically insert the results of rounding, the system will not error; fixed-point numbers if the precision and scale are not written, the default value of decimal (10,0) is applied, and if the data exceeds the precision and scale values , the system will error.

Bit type, used to hold field values, bit (m) can be used to hold a multi-bit binary number, M range from 1~64, if not write the default is 1 bits. For bit fields, direct use of the SELECT command will not see the results, which can be used in bin ()

(shown in binary format) or hex () (shown in hexadecimal format) function to read.

Date Time Type:

There are several data types in MySQL that can be used for date and time representations, different versions may vary, and these data types are mainly distinguished as follows:

1. If the date is used to denote a month or year, it is usually represented by date. BYTE: 4, Minimum: 1000-01-01, Maximum: 9999-12-31

2. DateTime is usually used to denote a day or minute of a month or minute. BYTE: 8, Min.: 1000-01-01 00:00:00, Maximum: 9999-12-31 23:59:59

3. When used to represent seconds and minutes, it is usually indicated by time. BYTE: 4, min: -838:59:59, Max: 838:59:59

4. If a frequent insert or update time is required for the current system time, it is usually represented by timestamp. The timestamp value returns a string that appears in "Yyy-mm-dd HH:MM:SS" format, displays a fixed width of 19 characters, and if you want to get a numeric value, add "+0" to the timestamp column. BYTE: 4, Minimum: 19700101080001, Maximum: sometime in 2038

5. If the year is expressed in, you can use year to indicate that it takes up less space than date, and years has a 2-bit or 4-bit format, and the default is 4-bit format. In 4-bit format, the allowed values are: 1901-2155 and 0000. In 2 bits, the allowable value is: 70-69, which represents 1970-2,069. MySQL Displays the year value in yyyy format (starting with 5.5.27, 2-bit format is not supported). BYTE: 1, Minimum: 1901, Maximum: 2155

Each datetime type has a valid range of values, and if this range is exceeded, the system will be sqlmode by default, and will be stored with a value of 0.

Timestamp also has an important feature that is related to time zones. When the date is inserted, it is converted to the local time zone, and when it is removed from the database, it is also necessary to convert the date to the local time zone. This way, users in two different time zones may see the same date differently.

Data types for MySQL