Basic data types for MySQL

MySQL database uses data types that have Numeric type

Integer type
Tinyint a tiny integer that occupies 1 bytes by default is the signed integer value range is-128-127 if you need to set an unsigned small integer to be preceded by the type with the unsigned keyword unsigned value range is 0-255
smallint a small integer that occupies 2 bytes by default is the signed integer value range is-32768-32767 if you need to set an unsigned small integer to be preceded by the type unsigned the unsigned value range is 0-65535
Mediumint occupies a 3-byte integer By default is the signed integer value range is-2 of 23 Square to 2 of 23 square-1 if required to be set to an unsigned integer need to be appended with the unsigned keyword unsigned value range is 0-2 of 24 square-1
int occupies a large integer of 4 bytes By default is the signed integer value range is-2 of 31 to 2 of the 31-square-1 if you need to set an unsigned large integer to be followed by the type unsigned unsigned value range is 0-2 of 32 square-1
BigInt occupies a maximum integer of 8 bytes By default is the signed integer value range is-2 of 63 square to 2 63-square-1 if you need to set an unsigned large integer to be preceded by the type unsigned unsigned value range is 0-2 of 64 square-1
Integer type can specify the display width, does not affect the type storage range, int (5) indicates a display width of 5 when less than 5 bits with a space on the left side to add, if you need to supplement with 0, you need to add zerofill keyword

mysql> create table _123.t2(age tinyint(3) zerofill) ;mysql> insert into _123.t2 values(12);mysql> select * from _123.t2;+------+| age  |+------+|  012 |+------+1 row in set (0.00 sec)

Floating-point type
Float occupies a 4-byte single-precision floating-point number defined in the form of float (m,n) m for the total number of digits, the n-bit scale length float is a non-standard type, and the approximate value is stored in the database
Double 8-byte dual-precision floating-point number double (m,n) m for total digits length, n-bit scale length double non-standard type, approximate value saved in database
Decimal fixed number
* * Floating-point number if you do not write longitude and scale, will be in accordance with the actual accuracy of the value of the save, if there is precision and scale, it will automatically insert the results after rounding, the system will not error; If the precision and scale are not written, the default value decimal (10,0), if the data exceeds the precision and scale values, The system will error.

Character Type * *

Char is fixed to a maximum of 255 characters, and the number of characters is less than the defined length when the placeholder is added to the right with a space
varchar variable-length character type up to 65,535 characters, based on the length of the number of characters to open up storage space, because before storage needs to calculate storage space so storage speed is slower than char, in practical applications should avoid a lot of the use of varchar type
Text/blob is used when the number of characters is greater than 65535, can store video, audio, pictures and other files but it is not recommended to use a database to store this data, generally use the path of the storage file link to physical storage space

Time Date Type

Year default assignment format YYYY four-digit year 1 bytes
Date Date default assignment format is YYYYMMDD Four-bit year two-bit month two-bit date 4 bytes
Time default assignment format is HHMMSS two-bit-hour two-bit minute two-bit-second 3-byte
DateTime Date Time default assignment format YYYMMDD HHMMSS 8 byte value range is 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59.99 If you do not assign a value to a field of that type, empty instead of
time Stamp datetime default assignment Format YYYMMDD HHMMSS 4 byte value range is 1970-01-01 00:00:00 ~ 2038-1-19 03:14:07.99 If the field of the type is not assigned a value, the current system time is assigned
I Nsert into _123.T4 values ("tt", 1990,083000,19900921,20180228193000);
If you are using formatted time assignments, you need to insert them as strings (in quotation marks)

  INSERT INTO _123.T4 values ("TT", 1990, "08:30:00", "1990-09-21", "2018-02-28 19:30:00"); ' Query OK, 1 row ' Affected (0.02 sec ") # Several functions about time get the current system date Time function now () Sysdate () now () at the beginning of the call to get the time sysdate () Gets the time of the execution when the Date function date () needs to pass in a DateTime parameter Date (20180909121212) Gets the 2018-09-09 if you need to get the current date (now ()) or curdate () to get the time function () need to pass in a datetime parameter. Time (20180909 121212) Get the 12:12:12 if you need to get the current system time (now ()) or curtime () to get the function of the year () to get the month of the function of the date Time parameter () that needs to pass in a datetime parameter to get the day's function days () a function that needs to pass in a datetime parameter to get the Hour hour () needs to pass in a datetime parameter to get the minute function minute () need to pass in a datetime parameter to get the second function second () need to pass in a datetime parameter-----# Enumeration type the value of the enumeration type is to select the set multiple selection type set (Value list) in the specified value List creat table Db1.t1 ("Boy", "Girl")) when inserting the value of the sex field can only be "boy" or "Girl" One of the enum type enum (Value list) creat table Db1.t2 (Love Set ("film", "Girl", "game")) inserts the value when the Love Field can only be "film" or "Girl" or "game" One or more of the  

Mysql>create table _123.T5 (name char), sex enum ("Boy", "Girl"), Love Set ("film", "Music", "Girl"));
Mysql>insert into _123.T5 values ("Bob", 1, "Film,girl");
Mysql> select * from _123.t5;
+------+------+-----------+
| name | sex | Love |
+------+------+-----------+
| Bob | Boy | Film,girl |
+------+------+-----------+

Basic data types for MySQL