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

This article mainly introduces "the introduction and use of Mysql data types". In daily operation, I believe many people have doubts about the introduction and use of Mysql data types. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "introduction and use of Mysql data types". Next, please follow the editor to study!

Transaction is one of the most important concepts in database, so the last part focuses on the use of transaction in database, and gives an example of how to use transaction to operate database in actual development. We also know that there are many data types in Mysql, and most people probably just know how to use these different data types, so this article focuses on the different data types in Mysql.

Mysql data type classification

There are four common data types in Mysql: string data type, date / time data type, numeric data type, and binary data type.

String data type: it mainly includes the following data types: char,varchar,tinytext,mediumtext,text,longtext,enum,set.

Date / time data type: it mainly contains the following data types: date,time,datetime,timestamp,year.

Numeric data type: it mainly contains the following data types: tinyint,smallint,mediumint,int,bigint,float,double,decimal.

Binary data type: it mainly contains the following data types: tityblob,blob,mediumblob,longblob.

Constraint condition

Before we begin to formally explain data types, we need to understand how to add some basic constraints to fields. The common constraints that Mysql can add to fields are: unsigned,zerofill,not null,default,primary key,auto_increment,unique key,foreign key.

Unsigned: unsigned type, plus this constraint value cannot be negative and can only be applied to numeric type data columns.

Zerofill: when the length of the data display is not enough, add 0 to the specified length in front of the data, and the field will automatically add a unsigned constraint.

Not null: this is easy to understand. Add a non-null constraint to the field.

Default: if the insert data does not specify a value, the default value is used.

Primary key: add a primary key constraint to a field. A table can have only one primary key, but it can be combined with other fields to form a primary key, which is generally used in conjunction with auto_increment constraints.

Auto_increment: only works on numeric types. Fields can be incremented automatically, starting with 1 by default. Generally used in conjunction with primary key.

Unique key: sets a unique constraint, then the value of the field cannot be duplicated, except for null.

Foreign key: foreign key constraints, ensuring data integrity and uniqueness, and multi-table concatenation table operations.

String type

Char data type

* * definition: * * you can define a fixed-length string whose length ranges from 1 to 255characters. The length must be specified when the table is created, otherwise it will be defined as char (1) by default. If the string does not reach the specified length, it will be filled with spaces to the specified length. * * usage: * * char data type can be used when we need to involve some fixed-length data columns, such as mobile phone number using char (11), ID card number using char (18), user gender using char (3).

Varchar data type

* * definition: * * you can define a variable-length string that theoretically stores up to 255 bytes of data. However, if you specify varchar (x) when you create it, you can only store data of no more than x characters. * * the difference compared to char type: * * in fact, we use varchar data type more frequently, because char type will occupy the defined number of bytes regardless of the length of the stored data, but varchar will only occupy the actual string length + 1 byte. However, char queries are more efficient than varchar, so we can give priority to the char data type when storing fixed length. Usage: varchar types can be used for data columns of variable length, such as names, and general Chinese names of 2 to 5 words, so we define them as varchar (15).

Tinytext data type

* * definition: * * tinytext is also a variable-length string, which can store up to 255bytes of variable-length text. * * disadvantages: * * default values are not allowed for tinyint data type definition, and there is no case conversion in retrieval, and the efficiency is lower than that of char and varchar. Therefore, this data type is rarely used in general.

Mediumtext data type

* * definition: * * mediumtext is also a variable-length string, which can store up to 16k bytes of variable-length text. * * disadvantages: * * both mediumtext and tinytext belong to the text series data type, so the disadvantages are the same. * * how to use it: * * mediumtext is generally used to store long text operations when the varchar storage range does not match. In general, using mediumtext to store long text can satisfy the operation.

Text data type

* * definition: * * text is also a variable-length string, which can store up to 64k bytes of variable-length text. * * disadvantages: * * text also belongs to the text series data type, so the disadvantages are the same as above. * * how to use it: * * text is generally used to store long text operations when the mediumtext storage range does not match, and for general user articles.

Longtext data type

* * definition: * * longtext is also a variable-length string, which can store up to 4G bytes of variable-length text. * * disadvantages: * * longtext also belongs to the text series data type, so the disadvantages are the same as above. * * how to use it: * * longtext can store plain text with a maximum length of no more than 4G, but this data type is hardly needed in general.

Enum data type

* * definition: * * ENUM is a string object. You can limit the value range of a field through ENUM. If the value of the field is not one of the optional values when inserting data, an empty string or NULL replaces the value that the user wants to insert. The maximum length of the enum collection is 64K. * * disadvantages: * * it is not recommended to use ENUM to limit the range of values in the database, because there are actually many pits. For example, ENUM takes values through corner marks, but the corner marks start from 1, because 0 is left for empty strings, or 0 and "0" are not the same in ENUM. If you use 0 as a corner mark to operate, because the ENUM angle mark starts from 1, an error will be reported. If you use "0" to operate, the empty string will be inserted at last. Because corner sign 0 is reserved for empty strings. Therefore, it is not recommended to use ENUM to restrict the range of field values at the database level. * * usage: * * for example, when creating a table, we can use ENUM to limit the value range to male or female.

Set data type

* * definition: * * data columns defined as set can store collections. The maximum length of set collections is 64k. * * the difference between set and enum: * * enum defines the range of values for data columns, but insert values can always have only one value. However, set can set the range of values for data columns, but you can insert multiple values when inserting values. How to use it: if we need to save the collection data, we can set the field to the set collection and then set a value range, and then take multiple values within the value range to form a collection for insertion. no, no, no.

Date / time data type

Mysql has a variety of data types that can be used to store time or dates. For example, we can use date to store dates, we can use year to store years, and we can use time to store time. You can use datetime or timestamp to store a combination of dates and times, so let's take a look at these date / time data types.

Date data type

* * definition: * * is used to store dates, ranging from '1000-01-01' to '9999-12-31'. * * how to use it: * * it can be used to store data columns of year, month and day. For example, we can use the date data type to store the date of a user's birth.

Time data type

* * definition: * * is used to store time. It can be used to represent not only the time of the day, but also the time interval between two times. The range of values is'- 838 / 58 / 59 / 59 to '838 / 59 / 59 / 59'. How to use it: many friends may doubt their eyes here. Time values range from'- 838 / 58 / 59 / 59 to '838 / 59 / 59 / 59 / 58 / 59 / 59 / 5. In fact, the range of values can be understood like this: d HH:MM:SS, that is, if you want to store the interval, you will multiply the interval by the hour as an hour. Because of the complexity of using this time, let's use SQL statements here to see the insertion effect of several different format times:

Simply insert a current time effect is to save time:

With only one colon, only hours are saved and seconds are saved as 00:

If there is no colon, the time is saved as 00, and only seconds are saved:

Adding a-2 before the time indicates that the event started two days ago, so the save result hour is-2 * 24 + 1 and finally saved as-49:00:00:

Adding a 2 before the time indicates that the event starts two days later, so the save result hour is 2 * 24 + 1 and finally saved as 49:00:00:

You can save with numbers directly, that is, without entering spaces and colons:

Year data type

* * definition: * * this is very simple, which is to save an annual value. * * usage: * * for example, if we need to record the year in which the book was published, we can use the year data type.

Datetime data type

* * definition: * * you can use datetime to save the time and date combination format, which ranges from 1000-01-01 00:00:00 to 2039-01-19 11:14:07. There are generally two ways to save yyyy-mm-dd HH:MM:SS or yyyymmddHHMMSS. * * how to use it: * * this is very common, such as the time when the order is issued or the time when the order is paid.

Timestamp data type

* * definition: * * timestamp is actually similar in function to datetime, but has a smaller range, with timestamp storage ranges from 1970-01-01 00:00:00 to 9999-12-31 23:59:59. * * how to use it: * * this is very common, such as the time when the order is issued or the time when the order is paid.

Numeric data type

In fact, I just mentioned that there are many types of numerical data, different types have different storage ranges, and the required storage space is also different. All numerical types can be signed, and positive and negative values can be set.

Tinyint data type

* * definition: * * stores integer data with a size of 1 byte. Values range from-128 to 127 if symbolic values are saved, and from 0 to 255 if unsigned values are saved.

Smallint data type

* * definition: * * stores integer data with a size of 2 bytes. Values range from-32768 to 32767 if symbolic values are saved, and from 0 to 65535 if unsigned values are saved.

Mediumint data type

* * definition: * * stores integer data with a size of 3 bytes. Values range from-8388608 to 8388607 if symbolic values are saved, and from 0 to 16777215 if unsigned values are saved.

Int data type

* * definition: * * stores integer data with a size of 4 bytes. Values range from-2147683648 to 2147683647 if symbolic values are saved, and from 0 to 4294967295 if unsigned values are saved.

Bigint data type

* * definition: * * stores integer data with a size of 8 bytes. Values range from-2 ^ 63 to 2 ^ 63-1 if symbolic values are saved, and from 0 to 2 ^ 64-1 if unsigned values are saved.

Float data type

* * definition: * * stores floating-point data with a size of 4 bytes. Unsigned cannot be set for floating-point data. Values range from-1.175494351e-38 to 1.175494351e-38.

Double data type

* * definition: * * store floating-point data with a size of 8 bytes. Unsigned cannot be set for floating-point data. The precision is higher than that of float. Values range from-2.2250738585072014e-308 to 2.2250738585072014e-308.

Decimal data type

* * definition: * * is often used to store precise decimal places. You can set the number of bytes stored and the number of decimal places retained. The maximum number of bytes stored is 65, the default is 10, the maximum number of decimal places is 30, and the default is 0. * * how to use: * * you can often use decimal to save the amount or integral value, because the amount is usually kept in a fixed decimal place.

Binary data type

* * definition: * * binary data type can store any data, not only text data, but also image or multimedia data.

In fact, binary data types are less used than other data types, because files are usually uploaded to oss for cdn acceleration. There are four data types: tinyblob,blob,mediumblob,longblob. The difference between these data types lies in the storage scope.

Tinyblob: the maximum storage length is 255bytes.

Blob: the maximum storage length is 64k.

Mediumblob: the maximum storage length is 16m.

Longblob: the maximum storage length is 4G.

* * disadvantages: * * excessive storage of files will affect the performance of the database.

At this point, the study on "introduction and use of Mysql data types" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.