MySQL data type

Data type

truncate t1  (delete table) # unsigned type ALTER TABLE t1 modify ID tinyint unsigned (table has value, cannot be changed directly) desc T1

========================================tinyint[(m)] [unsigned] [zerofill] small integer, data type is used to hold some range of integer numeric ranges: Signed:-128 ~ 127Unsigned : 0 ~255No Boolean value in Ps:mysql, using tinyint (1) constructs. ========================================int[(M)][unsigned][zerofill] integer, data type is used to hold some range of integer numeric ranges: Signed:-2147483648 ~ 2147483647Unsigned : 0 ~4294967295========================================bigint[(M)][unsigned][zerofill] Large integer, data type used to hold some range of integer numeric ranges: Signed:-9223372036854775808 ~ 9223372036854775807Unsigned : 0 ~18446744073709551615

Note: When you specify a width for this type, you only specify the display width of the query results, regardless of the storage scope, as follows

In fact, we don't have to specify a display width for an integer type, so you can use the default.

The default display width, which is based on the maximum value plus 1

The storage width of int is 4 bytes, or 32 bit, i.e. 2**32

The unsigned maximum value is: 4294967296-1

Signed Maximum: 2147483648-1

Signed and unsigned maximum numbers require a display width of 10 and 11 bits for a signed minimum value to display completely, so the default display width of int type is 11 is very reasonable

Finally: the type of shaping, in fact, there is no need to specify the display width, using the default is OK

Does not limit the bytes stored

Zerofull Not enough width to fill with 0

2, floating-point type

Fixed-point number type Dec equals decimal

Float type: float double

Function: Store salary, height, weight, physical parameters, etc.

======================================#float[(m,d)] [UNSIGNED] [Zerofill]definition: Single-precision floating-point numbers ( non-accurate decimal values ), M is the total number of numbers, D is the number of decimal places. The M maximum value is 255,d with a maximum value of 30 signed:-3.402823466E+38 to-1.175494351e-38,           1.175494351E-38 to 3.402823466E+38Unsigned :1.175494351E-38 to 3.402823466E+38accuracy:As the number of decimals increases, the accuracy becomes inaccurate ****======================================#double[(m,d)] [UNSIGNED] [Zerofill]definition: Double-precision floating-point number (non-accurate decimal value), M is the total number of digits, D is the number after the decimal point. The M maximum value is 255,d with a maximum value of 30 signed:-1.7976931348623157E+308 to-2.2250738585072014e-308 2.2250738585072014E-308 to 1.7976931348623157E+308Unsigned :2.2250738585072014E-308 to 1.7976931348623157E+308accuracy:**** With the increase in decimals, accuracy is higher than float, but it can also become inaccurate****======================================decimal[(m[,d]) [unsigned] [Zerofill] (most precise) definition: Accurate decimal value, M is the total number of digits (minus sign), D is the number of decimal points. The M maximum value is 65,d maximum of 30. Accuracy: * * * * with the increase of decimals, accuracy is always accurate * * * For accurate numerical calculations, this type of decaimal is used to store exact values because they are stored internally as strings. 

mysql> CREATE table T1 (x float (256,31)); ERROR1425 (42000): Too big Scale to specified forColumn'x'. Maximum is30. mysql> CREATE table T1 (x float (256,30)); ERROR1439 (42000): Display width out of range forColumn'x'(max = 255) MySQL> CREATE table T1 (x float (255,30));#Build Table SuccessQuery OK, 0 rows affected (0.02sec) MySQL> CREATE table t2 (x double (255,30));#Build Table SuccessQuery OK, 0 rows affected (0.02sec) MySQL> CREATE table t3 (x decimal (66,31)); ERROR1425 (42000): Too big Scale to specified forColumn'x'. Maximum is30. mysql> CREATE table t3 (x decimal (66,30)); ERROR1426 (42000): Too-big Precision Specified for 'x'. Maximum is65. mysql> CREATE table t3 (x decimal (65,30));#Build Table SuccessQuery OK, 0 rows affected (0.02sec) MySQL>show tables;+---------------+| TABLES_IN_DB1 |+---------------+| T1 | | T2 | | T3 |+---------------+rowsinchSet (0.00sec) MySQL> INSERT into T1 values (1.1111111111111111111111111111111);#31 digits after decimal 1Query OK, 1 row affected (0.01sec) MySQL> INSERT into t2 values (1.1111111111111111111111111111111); Query OK,1 Row Affected (0.00sec) MySQL> INSERT into T3 values (1.1111111111111111111111111111111); Query OK,1 row affected, 1 warning (0.01sec) MySQL> select * fromT1;#with the increase of decimals, accuracy begins to be inaccurate+----------------------------------+| X |+----------------------------------+| 1.111111164093017600000000000000 |+----------------------------------+RowinchSet (0.00sec) MySQL> select * fromT2;#accuracy is more accurate than float, but as the number of decimals increases, it becomes inaccurate.+----------------------------------+| X |+----------------------------------+| 1.111111111111111200000000000000 |+----------------------------------+RowinchSet (0.00sec) MySQL> select * fromT3;#accuracy is always accurate, D is 30, so only 30 decimal places are left+----------------------------------+| X |+----------------------------------+| 1.111111111111111111111111111111 |+----------------------------------+RowinchSet (0.00 sec)

Validation

Iii. Type of date

Date Time datetime timestamp tear

Role: Store user registration time, article release time, employee entry time, birth time, expiration time, etc.

year            YYYY (1901/2155)        DATE            YYYY-mm-dd (1000-01-01/9999-12-31)        time            HH:MM:SS ( ' -838:59:59 '/'838:59:59')        DATETIME            YYYY-mm-dd hh:mm: SS (1000-01-01 00:00:00/9999-12-31 23:59:59    Y)        TIMESTAMP            YYYYMMDD hhmmss (1970-01-01 00:00:00/2037 years at a time)

View Code

============year===========MariaDB [DB1]> CREATE TABLE T10 (Born_year year);#whichever width is specified for year, the last is year (4)MariaDB [db1]>INSERT INTO T10 values(1900),    (1901),    (2155),    (2156); MariaDB [DB1]> select * fromT10;+-----------+|      Born_year |+-----------+|      0000 | |      1901 | |      2155 | | 0000 |+-----------+============date,time,datetime===========MariaDB [DB1]>CREATE TABLE T11 (d date,t time,dt datetime); MariaDB [DB1]>desc T11;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| D | Date |     YES | |       NULL | || T | Time |     YES | |       NULL | || DT | datetime |     YES | |       NULL | |+-------+----------+------+-----+---------+-------+MariaDB [DB1]>INSERT into T11 values (now (), now (), now ()); MariaDB [DB1]> select * fromT11;+------------+----------+---------------------+| D | T | DT |+------------+----------+---------------------+| 2017-07-25 | 16:26:54 | 2017-07-25 16:26:54 |+------------+----------+---------------------+============timestamp===========MariaDB [DB1]>CREATE TABLE T12 (time timestamp); MariaDB [DB1]>INSERT into T12 values (); MariaDB [DB1]>INSERT INTO T12 values (NULL); MariaDB [DB1]> select * fromT12;+---------------------+| Time |+---------------------+| 2017-07-25 16:29:17 | | 2017-07-25 16:30:01 |+---------------------+============ attention, attention, attention, ===========1when inserting a time separately, you need to insert it in the form of a string, in the corresponding format2when inserting a year, use 4-bit values as much as possible3. When inserting a two-bit year, the <=69, starting with 20, such as 50, results 2050>=70, starting with 19, such as 71, result 1971MariaDB [DB1]>CREATE table T12 (y year); MariaDB [DB1]>INSERT INTO T12 values(50),    (71); MariaDB [DB1]> select * fromT12;+------+| Y |+------+| 2050 | | 1971 |+------+============ Comprehensive Exercise ===========MariaDB [DB1]>CREATE TABLE Student (-ID int,Name varchar (20),    -born_year Year,-Birth date,-class_time time,-reg_time datetime); MariaDB [DB1]>INSERT into student values(1,'Alex',"1995","1995-11-11","11:11:11","2017-11-11 11:11:11"),    (2,'Egon',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12"),    (3,'WSB',"1998","1998-01-01","13:13:13","2017-01-01 13:13:13"); MariaDB [DB1]> select * fromstudent;+------+------+-----------+------------+------------+---------------------+| ID | name | Born_year | Birth | Class_time |    Reg_time |+------+------+-----------+------------+------------+---------------------+| 1 |      Alex | 1995 | 1995-11-11 | 11:11:11 |    2017-11-11 11:11:11 | | 2 |      Egon | 1997 | 1997-12-12 | 12:12:12 |    2017-12-12 12:12:12 | | 3 |      WSB | 1998 | 1998-01-01 | 13:13:13 | 2017-01-01 13:13:13 |+------+------+-----------+------------+------------+---------------------+Validation

View Code

In many of the scenarios that are actually applied, both of these date types are available to meet our needs, with storage accuracy of seconds, but in some cases they show their merits and demerits. Here's a summary of the differences between the two types of dates. 1. DateTime has a date range of 1001--9999 years, and the timestamp time range is 1970--2038 years. 2. DateTime storage Time is independent of time zone, timestamp storage time is related to time zone, and the displayed value depends on time zone. In the MySQL server, the operating system and client connections all have time zone settings. 3. DateTime uses 8 bytes of storage space, and timestamp has a storage space of 4 bytes. As a result, timestamp is more space efficient than DateTime. the default value for 4.DATETIME for null;timestamp fields is not empty by default (not null), the default is the current time (Current_timestamp), and if no special handling is done, And the UPDATE statement does not specify an updated value for the column, the default update is the current time. The difference between datetime and timestamp

View Code

MySQL data type