Basic Data Types of MySQL Databases

Basic Data Types of MySQL Databases

Integer type

Numeric data types are mainly used to store numbers, including:

TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), BIGINT.

The following example shows the data space occupied by various types. Note that my version is mysql5.6.28 and can be viewed using the following command:

Create table tmp1 (x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT );

After the execution is successful, use DESC to view the table structure. The result is as follows:

DESC tmp1;

Create table tmp2 (x FLOAT (5, 1), y DOUBLE (5, 1), z DECIMAL (5, 1 ));

Insert data into the table:

INSERT INTO tmp2 VALUES(5.12, 5.15, 5.123);

Show warnings;

SELECT * FROM tmp2;

Create table tmp3 (y YEAR );

Insert data into the table:

INSERT INTO tmp3 values(2010),('2010');

Insert into tmp3 values ('123 ');

After the statement is executed, MySQL provides a warning to view the error message using SHOW:

SHOW WARNINGS;

SELECT * FROM tmp3;

The value range of YEAR data in MySQL is 1901 ~ 2155. Here YEAR refers to YEAR (4), and YEAR (2 ). For YEAR (2), the display YEAR range is 1970 ~ 2070. In YEAR (2), 00 ~ 69 represents 2000 ~ May 2069.

Insert the YEAR values represented by two strings to the y field in the tmp3 table, which are '0', '00', '77 ', and '10' respectively. The SQL statement is as follows:

First, delete the data in the table:

DELETE FROM tmp3;

Insert data into the table:

INSERT INTO tmp3 values('0'),('00'),('77'),('10');

View results:

SELECT * FROM tmp3;

Delete from tmp3;

Insert data into the table:

INSERT INTO tmp3 values(0),(78),(11);

View results:

SELECT * FROM tmp3;

Create table tmp4 (t TIME );

Insert data into the table:

INSERT INTO tmp4 values('10:05:05 '), ('23:23'), ('2 10:10'), ('3 02'),('10');

View results:

SELECT * FROM tmp4;

The TIME type occupies 3 bytes and is displayed in the range of-838: 59: 59 "~ "838: 59: 59 ". Someone may wonder why the TIME type can be greater than 23. Because the TIME type can be used not only to save the TIME of a day, but also to save the TIME interval, it also explains why the TIME type can also have a negative value. The same as DATETIME type, TIME type can also display microsecond TIME, but during insertion, the database will also perform the truncation operation

Insert '20160901', 101112, '0', 111213 to table tmp4. The SQL statement is as follows:

First, delete the data in the table:

DELETE FROM tmp4;

Insert data into the table:

INSERT INTO tmp4 values('101112'),(111213),( '0');

Insert data to the table:

INSERT INTO tmp4 values ( 107010);

Show warnings;

SELECT * FROM tmp4;

Delete from tmp4;

Insert data into the table:

INSERT INTO tmp4 values (CURRENT_TIME) ,(NOW());

View results:

SELECT * FROM tmp4;

Create table tmp5 (d DATE );

Insert "YYYY-MM-DD" and "YYYYMMDD" format dates to the table:

INSERT INTO tmp5 values('1998-08-08'),('19980808'),('20101010');

View the insert result:

SELECT * FROM tmp5;

DATE occupies 3 bytes, And the displayed DATE range is "1000-01-01" to "9999-12-31 ".

Insert the YY-MM-DD and YYMMDD strings into the tmp5 table. The SQL statement is as follows:

First, delete the data in the table:

DELETE FROM tmp5;

Insert YY-MM-DD and YYMMDD format date to the table:

INSERT INTO tmp5 values ('99-09-09'),( '990909'), ('000101') ,('111111');

View the insert result:

SELECT * FROM tmp5;

If there are no special conditions and requirements, it is still performed in the standard "YYYY-MM-DD HH: MM: SS" format during input.

Insert the YY-MM-DD and YYMMDD numeric format date into the tmp5 table. The SQL statement is as follows:

First, delete the data in the table:

DELETE FROM tmp5;

Insert YY-MM-DD and YYMMDD numeric format dates to the table:

INSERT INTO tmp5 values (99-09-09),(990909), ( 000101) ,( 111111);

View the insert result:

SELECT * FROM tmp5;

Delete from tmp5;

Insert the current system date to the table:

INSERT INTO tmp5 values( CURRENT_DATE() ),( NOW() );

View the insert result:

SELECT * FROM tmp5;

Create table tmp6 (dt DATETIME );

Insert "YYYY-MM-DD HH: MM: SS" and "YYYYMMDDHHMMSS" format date to the table:

INSERT INTO tmp6 values('1998-08-08 08:08:08'),('19980808080808'),('20101010101010');

View the insert result:

SELECT * FROM tmp6;

DATETIME occupies 8 bytes, which is a date type that occupies the most space. It displays both the date and time. The valid date range is "1000-01-01 00:00:00" to "9999-12-31 23:59:59 ".

Before MySQL 5.5 (including MySQL 5.5), the date type of the database cannot be accurate to the microsecond level, and any microsecond value will be truncated by the database.

However, MySQL database provides the function MICROSECOND to extract the MICROSECOND value of a date.

Delete from tmp6;

Insert "YY-MM-DD HH: MM: SS" and "YYMMDDHHMMSS" format date to the table:

INSERT INTO tmp6 values('99-09-09 09:09:09'),('990909090909'),('101010101010');

View the insert result:

SELECT * FROM tmp6;

Delete from tmp6;

Insert YYYYMMDDHHMMSS and YYMMDDHHMMSS numeric formats to the table:

INSERT INTO tmp6 values(19990909090909), (101010101010);

View the insert result:

SELECT * FROM tmp6;

Delete from tmp6;

Insert the current system date to the table:

INSERT INTO tmp6 values( NOW() );

View the insert result:

SELECT * FROM tmp6;

Create table tmp7 (ts TIMESTAMP );

Insert data into the table:

INSERT INTO tmp7 values ('19950101010101'),('950505050505'),('1996-02-02 02:02:02'),('97@03@03 03@03@03'),(121212121212),( NOW() );

View the insert result:

SELECT * FROM tmp7;

Delete from tmp7;

Insert the current system date to the table:

INSERT INTO tmp7 values( NOW() );

View the date value in the current time zone:

Set time_zone = '+ 10:00 ';

View the date value at the time of insertion again:

SELECT * FROM tmp7;

Type_name (fsp)

The type_name type can be TIME, DATETIME, or TIMESTAMP. Fsp indicates that the precision of the fractional part of the second is supported. The maximum value is 6, indicating microsecond (microseconds). The default value is 0, indicating that there is no fractional part, it is also designed to be compatible with the TIME, DATETIME, and TIMESTAMP types in earlier versions. For time functions such as CURTIME (), SYSDATE (), and UTC_TIMESTAMP (), fsp is also supported. For example:

Create table tmp8 (ch CHAR (4), vch VARCHAR (4 ));

Input data:

INSERT INTO tmp8 VALUES('ab ', 'ab ');

Query results:

SELECT concat('(', ch, ')'), concat('(',vch,')') FROM tmp8;

Create table tmp9 (enm ENUM ('first', 'second', 'third '));

Insert column values:

INSERT INTO tmp9 values('first'),('second') ,('third') , (NULL);

View index values:

SELECT enm, enm+0 FROM tmp9;

Create table tmp10 (soc INT, level enum ('excellent', 'good', 'bad '));

Insert data:

INSERT INTO tmp10 values(70,'good'), (90,1),(75,2),(50,3);

Insert data again:

INSERT INTO tmp10 values (100,'best');

SELECT * FROM tmp10;

Create table tmp11 (s SET ('A', 'B', 'C', 'D '));

Insert data:

INSERT INTO tmp11 values('a'),( 'a,b,a'),('c,a,d');

Insert into tmp11 values ('a, x, B, y ');

MySQL provides a warning message because a value not supported by the SET column is inserted.

SELECT * FROM tmp11;

Create table tmp12 (B BIT (4 ));

Insert data:

INSERT INTO tmp12 VALUES(2), (9), (15);

Query the insert result:

SELECT BIN(b+0) FROM tmp12;

Create table tmp13 (B binary (3), vb varbinary (30 ));

Insert data:

INSERT INTO tmp13 VALUES(5,5);

View the length of data stored by two fields:

SELECT length(b), length(vb) FROM tmp13;

SELECT B, vb, B = '5', B = '5 \ 0 \ 0', vb = '5', vb = '5 \ 0 \ 0' FROM tmp13;

The execution result shows that the length of field B is different from that of field vb, because the space of field B is filled with '\ 0', while that of field vb is not filled.

Refer:

• MySQL date data type and time type usage Summary
• MySQL technology Insider: time and date data types