MySQL series of two data types and operators

A database table consists of multiple column fields, each of which specifies a different data type. After specifying the data type of the field, the data content inserted into the field is determined, for example, when you want to insert numeric values, you can store them as integer types, or they can be stored as string types, and different data types determine how MySQL is used to store them. And what operation symbols to choose when using them.

1. mysql Data type introduction

MySQL supports a variety of data types, primarily numeric types, date/time types, and string types.

(1) Numeric data type: Includes integer type tinyint, SMALLINT, Mediumint, INT, bigint, and floating point small data type float and double, fixed-point decimal type Decimal.

(2) Date/Time type: Includes year, hour, date, DateTime, and timestamp.

(3) String type: Includes char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, enum, set, and so on.

1.1, Integer type

Numeric data types are primarily used to store numbers, and MySQL provides multiple numeric data types. Different data types provide different ranges of values, and the greater the range of value that can be stored, the greater the storage space required. The main types of integers that MySQL provides are: TINYINT, SMALLINT, Mediumint, INT (integer), BIGINT. An attribute field of an integer type can add a auto_increment self-increment constraint condition.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/7C/B6/wKiom1bWnBGg2ceVAAE6jAkBflg024.png "title=" 1.png " alt= "Wkiom1bwnbgg2cevaae6jakbflg024.png"/>

As you can see from the table, the number of bytes required to store different types of integers is different, taking up the minimum number of bytes is the tinyint type, the largest of which is the bigint type, and the larger the range of values that the corresponding type with the more bytes can represent.

Depending on the number of bytes used to calculate the range of values for each data type, for example, tinyint requires 1 bytes (8 bits) to store, then the maximum value of tinyint unsigned number is 2^8-1, that is, the maximum 255;tinyint signed number is 2^7-1, or 127. The range of values for other types of integers is calculated the same way. As shown in the following:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/7C/B5/wKioL1bWnobj6DrrAAFj2yfBfZk500.png "title=" 2.png " alt= "Wkiol1bwnobj6drraafj2yfbfzk500.png"/>

#  such as:[hellodb]> create table text1  (a tinyint,b smallint,c  Mediumint,d int,e bigint); query ok, 0 rows affected  (0.42 sec) [hellodb]> desc text1 ;+-- -----+--------------+------+-----+---------+-------+| field | type          | null | key | default | extra |+------- +--------------+------+-----+---------+-------+| a     | tinyint (4)     | YES  |     | NULL    |        | |  b     | smallint (6)   | YES  |      | null    |       | |  c     | mediumint (9)  | YES  |     | null    |       | |  d     | int (one)       | yes  |      | null    |       | |  e     | bigint (   | YES  |  )    | null    |       |+-------+---- ----------+------+-----+---------+-------+5 rows in set  (0.00 sec)

As you can see, the system will add a different default display width. These display widths guarantee that each data type can be displayed with all values that are within the range of the values being taken. For example, the range of tinyint signed and unsigned numbers is 128-127 and 0-255, respectively, because the minus sign occupies one digit, so the default display width of tinyint is 4, and the default width of other integer types is the same as the minimum value of the signed number.

Note: The display width is only used for display, and cannot limit the range of values and occupy space. For example, int (3) consumes 4 bytes of storage space, and the maximum allowable value is not 999, but the maximum allowed for int integer type.

Different integer types have different ranges of values and require different storage space, so you should choose the most

The right type, which helps to improve query efficiency and save storage space. Integer types are numeric values that do not have fractional parts, and many places in real life require numbers with decimals.

1.2. Floating-point type and fixed-point number type

The number of floats and fixed points in MySQL is used to represent decimals. There are two kinds of floating-point types: single-precision floating-point types (float) and double-precision floating-point types (double). There is only one fixed-point type: DECIMAL. Both floating-point and fixed-point types are available (M,n), and several M are called precision, representing the total number of digits: N is the scale, which is the number of digits representing decimals. Specific reference:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/7C/B6/wKioL1bWsaGQRVDuAADrZcKngmE612.png "title=" 3.png " alt= "Wkiol1bwsagqrvduaadrzckngme612.png"/>

The decimal type differs from float and double,decimal in that it is actually stored as a string, and the maximum possible range of decimal values is the same as a double, but its valid range of values is determined by the values of M and D. If you change m and fix D, then its value range will be larger with M, as can be seen from the above graph, the storage space of decimal is not fixed, and by its precision value m, occupy m+2 bytes.

Note: Whether fixed-point or floating-point type, if the user-specified precision exceeds the precision range, it is rounded for processing.

#  such as:[hellodb]> create table text2  (A float (4,1), B DOUBLE (5,1),C  DECIMAL (5,1)); query ok, 0 rows affected  (0.43 sec) mariadb [hellodb]> desc  text2;+-------+--------------+------+-----+---------+-------+| field | type          | null | key | default | extra |+- ------+--------------+------+-----+---------+-------+| a     | float (4,1)    | yes  |     | null    |        | |  b     | double (5,1)   | YES  |      | null    |       | |  c     | decimal (5,1)  | YES  | &Nbsp;   | null    |       |+-------+ --------------+------+-----+---------+-------+3 rows in set  (0.00 sec) [hellodb]>  insert into text2 values (5.12,5.15,5.123); query ok, 1 row affected, 1 warning  (0.06 sec)    #有一个警告信息 [ hellodb]> show warnings;    #查看警告信息 +-------+------+-------------------------------- --------+| level | code | message                                  |+-------+------+----------------------------------------+| note   | 1265 | Data truncated for column  ' C '  at row 1  |+-------+------+----------------------------------------+1 row in set  (0.00 SEC) #可以看到FLOAT和DOUBLE在进行四舍五入时没有给出警告, and gives a warning that the value of the C field is truncated; [Hellodb]> select  * FROM text2;    #查看表信息 +------+------+------+| a    |  b    | c    |+------+------+------+|  5.1 |   5.2 |  5.1 |+------+------+------+1 row in set  (0.01  Sec

# float and double when you do not specify the precision, the default is the actual precision, and decimal does not specify the precision by default (10,0).

Floating-point numbers are somewhat relative to the fixed-point number, and floating-point numbers can represent a larger range of data in the case of a certain length: its disadvantage is that it can cause accuracy problems.

In MySQL, the fixed-point number is stored as a string, when the accuracy requirements are relatively high (such as currency, scientific data, etc.) using the type of decimal is better, the other two floating-point numbers are also easy to subtract and compare the problem, so in the use of floating point type should be noted, and try to avoid floating-point comparison.

1.3. Date and Time type

There are several data types that represent dates in MySQL, mainly: DATETIME, date, TIMESTAMP, time, and year. For example, you can only use the year type when you are logging only annual information, and you do not need to use date. Each type has a valid range of values, and the system inserts a value of "0" into the database when you specify a value that is indeed not valid. The date and time data types are as follows:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/7C/F6/wKiom1bdRinjoXnlAAFwT0Iu-z0036.png "title=" 4.png " alt= "Wkiom1bdrinjoxnlaafwt0iu-z0036.png"/>

#例如: 1, Year[hellodb]> create table text3 (y year);     #创建表text3Query  OK, 0 rows affected  (0.40 sec) [Hellodb]> insert into text3  values (2010), (' 2010 '), (' 2166 '); query ok, 3 rows affected, 1 warning  (0.22 sec) Records: 3   duplicates: 0  warnings: 1# insert data, MySQL gives a warning to view the warning message with show warnings [hellodb]>  show warnings;+---------+------+--------------------------------------------+| level    | Code | Message                                      |+---------+------+--------------------------------------------+|  warning | 1264 | out of range value for column&nbSP; ' Y '  at row 3 |+---------+------+--------------------------------------------+1 row  in set  (0.00 sec) #可以看到2166超过了YEAR类型的取值范围 but plugged in [hellodb]> select *  from text3;+------+| y    |+------+| 2010 | |  2010 | |  0000 |    #插入值超过取值范围, so instead of 0000+------+3 rows in set  (0.00 sec ) [hellodb]> delete from text3; query ok, 3 rows affected  (0.03 sec) #清空表内容 [Hellodb]> insert into  text3 values (' O '), (' 00 '), (' 77 '), (' 10 '); query ok, 4 rows affected, 1 warning  (0.03 sec) Records: 4   duplicates: 0  warnings: 1# inserting a two-bit string test [hellodb]> select * from  text3;+------+| y    |+------+| 0000 | |  2000 | |  1977 | |  2010 |+------+4 ROWS&Nbsp;in set  (0.01 sec) 2, TIME[HELLODB]> CREATE TABLE TEXT4 (T TIME); query ok, 0 rows affected  (0.19 sec) [hellodb]> insert into  text4 values  (' 10:05:05 '), (' 23:23 '), (' 2 10:10 '), (' 3 02 '), (' 10 '); query ok, 5 rows affected  (0.01 sec) records: 5  duplicates:  0  warnings: 0[hellodb]> select * from text4;+----------+| t         |+----------+| 10:05:05 | |  23:23:00 | |  58:10:00 | |  74:00:00 | |  00:00:01 |+----------+5 rows in set  (0.00 sec) #注: When using the ' d hh ' format, The hour must use a double-digit value, and if it is less than 10 hours, it should be preceded by an additional 0. [hellodb]> delete from text4;  #清空表内容Query  OK, 5 rows affected  (0.02 SEC) [Hellodb]> insert into text4 values (Current_time), (now ()); Query ok, 2 rows affected, 1 warning  (0.02 sec) Records: 2   duplicates: 0  warnings: 1# Insert Current Time [hellodb]> select * from  text4;+----------+| t        |+----------+| 17:41:29 | |  17:41:29 |+----------+2 rows in set  (0.00 sec) #注: A valid value beyond the time range is converted to the closest endpoint, and the invalid is converted to "00:00:00". 3, DATE[HELLODB]> CREATE TABLE TEXT5 (d date); query ok, 0 rows affected  (0.40 sec) [hellodb]> insert into  Text5 values (' 1998-09-09 '), (' 19980909 '), (' 20101010 '); query ok, 3 rows affected  (0.03 sec) records: 3  duplicates:  0  warnings: 0[hellodb]> select * from text5;+------------+| d           |+------------+| 1998-09-09 | | 1998-09-09 | |  2010-10-10 |+------------+3 rows in set  (0.00 sec)   # Different types of date values are correct and can be inserted into the table [hellodb]> delete from text5; query ok, 3 rows affected  (0.02 sec) [hellodb]> insert into  Text5 values (Current_date ()), (now ()); query ok, 2 rows affected, 1 warning  (0.02 sec) Records: 2   duplicates: 0  warnings: 1# inserts the current date #current_date only returns the current date value, excluding the time portion. The #NOW () function returns a date and time value that retains only its date part when it is saved to the database. [Hellodb]> select * from text5;+------------+| d           |+------------+| 2016-03-07 | |  2016-03-07 |+------------+2 rows in set  (0.01 sec)

This article is from the "Ask Heaven" blog, please make sure to keep this source http://79076431.blog.51cto.com/8977042/1748483

MySQL series of two data types and operators