Shulou(Shulou.com)06/01 Report--

This article mainly introduces what are the basic data types of mysql. The content of the article is carefully selected and edited by the author. It has a certain pertinence and is of great significance to everyone's reference. Let's learn about the basic data types of mysql together with the author.

Numerical type

Integer type

Tinyint occupies a small integer of 1 byte by default. The value range of signed integer is-128127. if you need to set it to unsigned small integer, you need to add the unsigned keyword after the type.

Smallint occupies 2 bytes of small integers by default. The value range of signed integers is-32768-32767. If you need to set it to unsigned small integers, you need to add the unsigned keyword after the type. The unsigned range is 0-65535.

Mediumint occupies 3 bytes of medium integer by default. The value range of signed integer is-2 to the 23 power of 2-1. If you need to set it to an unsigned integer, you need to add the unsigned keyword after the type. The unsigned value range is 0-2 to the power of 24-1.

Int occupies 4 bytes of large integers by default. The value range of signed integers is-2 to the 31st power of 2-1. If you need to set large unsigned integers to the type, you need to add unsigned unsigned integers to the 32 power of 0-2.-1.

Bigint occupies 8 bytes of maximum integers by default. The value range of signed integers is-2 to the 63rd power of 2-1. If you need to set large unsigned integers to the type, you need to add unsigned unsigned integers to the type. The value range is 0-2 to the 64th power-1.

Integer types can specify the display width without affecting the storage range of the type. Int (5) indicates that the display width is 5. If it is less than 5 digits, it is supplemented with spaces on the left. If you need to add 0, you need to add the zerofill keyword.

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

Floating point type

Float takes up 4 bytes of single-precision floating-point numbers defined as float (mreary n) m is the total digit length, n-place decimal place length float is a non-standard type, and approximate values are stored in the database

Double occupies 8 bytes of double-precision floating-point number double (m) m is the total digit length, n-place decimal place length double is a non-standard type, what is stored in the database is an approximate value

Decimal fixed point number

* * if the floating point number does not write longitude and scale, it will be saved according to the actual precision value. If there is precision and scale, the rounded result will be automatically inserted, and the system will not report an error; if the fixed point number does not write precision and scale, it will operate according to the default value decimal (10c0). If the data exceeds the precision and scale value, the system will report an error.

Character type * *

The character type of char fixed length is up to 255characters. If the number of characters is not enough for the defined length, space will be used to fill the space on the right.

The maximum length of varchar variable-length character types is 65535 characters, and the storage space is opened up according to the length of the number of characters. Because the storage space needs to be calculated before storage, the storage speed will be slower than char, so a large number of varchar types should be avoided in practical applications.

Text/blob is used when the number of characters is greater than 65535. It can store video, audio, pictures and other files, but it is not recommended to use a database to store these data. Generally, the path to the storage file is used to link to the physical storage space.

Time date type

Year year default assignment format YYYY four-digit year 1 byte

The default date date assignment format is YYYYMMDD four-digit year, two-digit month, two-digit date, 4 bytes.

The default assignment format of time time is hhmmss two-bit hour, two-bit minute, two-bit second, 3 bytes.

Datetime date and time default assignment format YYYMMDD hhmmss 8-byte value range is 1000-01-01 00:00:00 ~ 9999-12-31 23 59displacement 59.99If fields of this type are not assigned, null is used instead.

Timestamp date and time default assignment format YYYMMDD hhmmss 4-byte value range is 1970-01-01 00:00:00 ~ 2038-1-19 03 1415 07.99 if a field of this type is not assigned, it will be assigned using the current system time

Insert into _ 123.t4 values ("tt", 1990, 083000, 19900921, 20180228193000)

If you are using a formatted time assignment, you need to insert it as a string (enclosed 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 () get the time at the beginning of the call sysdate () get the time of execution the date function date () need to pass a date time parameter date (20180909121212) to get the current date date (20180909121212) if you need to get the current date Now () or curdate () to get the time function time () needs to pass a date-time parameter time (20180909121212) to get 12:12:12 if you need to get the time of the current system time (now ()) or curtime () the function year () of the year needs to pass in a date-time parameter to get the month function month () needs to pass a date-time parameter to get the date Function day () needs to pass a date-time parameter to get hours function hour () needs to pass a date-time parameter to get minutes function minute () needs to pass a date-time parameter to get seconds function second () needs to pass in a date-time parameter-# enumeration type-the value of the enumeration type is selected from the specified list of values Select set multiple selection type set (values list) creat table db1.t1 (sex enum ("boy") "girl") when the value is inserted, the sex field can only be "boy" or one of the enum radio types in "girl" enum (values list) creat table db1.t2 (love set ("film", "girl", "game")). When inserting values, the love field can only be "film" or "girl" or "game".

Mysql > create table _ 123.t5 (name char (32), 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 | |

+-+

After reading the above about the basic data types of mysql, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.