The sixth day of MySQL learning MySQL basic data type, the sixth day of mysql Learning

The sixth day of MySQL learning MySQL basic data type, the sixth day of mysql Learning

Do you still remember what I learned in the previous article? Do not remember to take a look at MySQL to learn basic MySQL database operations on the fifth day!

Data Types refer to the data features of columns, stored procedure parameters, expressions, and local variables. They determine the data storage mode and indicate different information types. Common Data Types in MySQL include numeric, date, time, and string.

I. numeric type

MySQL supports all numeric types in standard SQL, including strict data types (INTEGER, SMALLINT, DECIMAL, NUMBERIC), and approximate numeric data types (FLOAT, REAL, DOUBLE, PRESISION ), in addition, it is extended. Added TINYINT, MEDIUMINT, and BIGINT integers with different lengths and added BIT types to store BIT data.

(1). Integer

The five major integer types supported in MySQL are TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. These types are largely the same, and only the sizes of the stored values are different.

MySQL expands the SQL standard in the form of an optional display width indicator, so that when you retrieve a value from the database, you can extend this value to the specified length. For example, if you specify the type of a field as INT (6), you can ensure that the values containing less than 6 digits can be automatically filled with spaces when retrieved from the database. It should be noted that using a width indicator does not affect the field size and the range of values it can store. In case we need to store a number that exceeds the permitted range for a field, MySQL will truncate the end that is closest to the permitted range before storing it. Another special note is that MySQL will automatically change the value to 0 before the table is inserted with an unspecified value.

The following table shows the storage and range of each integer type:

The above definition is signed. Of course, you can also add the UNSIGNED keyword to define it as a non-Signed type, so the corresponding value range will be flipped over, for example: the value range of tinyint unsigned is 0 ~ 255.

For example, we use tinyint unsigned to store the age of a person:

CREATE TABBLE tb1(  age TINYINT UNSIGNED, ); 

(2). Floating Point Type

The three floating point types supported by MySQL areFLOAT and DOUBLEAndDECIMALType. FLOAT values are used to represent single-precision floating point values, while DOUBLE values are used to represent DOUBLE-precision floating point values. Like integers, these types also have additional parameters: a display width indicator and a decimal point indicator. For example, the FLOAT () statement specifies that the displayed value is no more than 7 digits, and the decimal point is followed by three digits. MySQL will automatically round the number of digits after the decimal point to the value closest to it and then insert it.

The DECIMAL data type is used in computation with very high precision requirements. This type allows you to specify the precision and counting method of a value as the selection parameter. The precision here refers to the total number of valid numbers saved for this value, and the counting method indicates the number of digits after the decimal point. For example, the DECIMAL () statement specifies that the stored value cannot exceed 7 digits, and the DECIMAL point cannot exceed 3 digits.

The following table lists their storage scopes:

I created a table in MySQL with a column FLOAT (5, 3). I did the following experiment:

1) insert 123.45678 and the final query result is 99.999;

2) Insert 123.456 and the final query result is 99.999;

3) Insert 12.34567 and the final query result is 12.346;

Therefore, when using the floating point model, you should pay attention to the traps. the actual result of inserting data into the database should prevail.

For example, the salary of our storage staff is FLOAT (8, 2) UNSIGNED:

CREATE TABBLE tb1(  salary FLOAT(8,2) UNSIGNED ); 

Ii. Date and Time types

When processing values of the date and time types, MySQL has five different data types available. They can be divided into simple date, time type, and mixed date, time type. According to the required precision, subtypes can be used in each shard type, and MySQL has a built-in function to convert diverse input formats into a standard format.

The following table describes five Date and Time types:

1) MySQL uses DATE and YEAR to store simple DATE values, and TIME to store TIME values. These types can be described as strings or integer sequences without delimiters. If the description is a string, values of the DATE type should be separated by the font size, while values of the TIME type should be separated by colons. It should be noted that the TIME type value without the colon separator will be understood by MySQL as the duration rather than the timestamp.

2) In addition to the date and time data types, MySQL also supports the DATEYIME and TIMESTAMP hybrid types. They can store dates and times as a single value. These two types are usually used to automatically store timestamps containing the current date and time, it can also play a good role in applications that need to execute a large number of database transactions and audit trails that require the establishment of a debugging and review purpose. If we do not explicitly assign values to fields of the TIMESTAMP type, or are assigned a NULL value. MySQL will automatically fill it with the current date and time of the system.

For example, we use the storage to add the user's Birthdate:

CREATE TABBLE tb1(  brithday DATE ); 

Iii. string type

MySQL provides six basic string types: CHAR, VARCHAR, TINYTEST, TEXT, MEDIUMTEXT, and LONGTEXT. It can store data ranging from a simple character to a huge text block or binary string.

The subscript explains six basic string types:

1). In CHAR (n) and VARCHAR (n) brackets, n represents the number of characters, not the number of bytes. Therefore, when you use Chinese characters (UTF8) it means that m Chinese characters can be inserted, but it actually occupies m * 3 bytes.

2) The biggest difference between CHAR and VARCHAR at the same time is that CHAR occupies n characters regardless of the actual VALUE, while VARCHAR only occupies the space + 1 that the actual characters should occupy, and the actual space is + 1 <= n.

3) the string is truncated when the n value exceeds the n value of CHAR and VARCHAR.

4) CHAR will truncate spaces at the end of the storage. VARCHAR and TEXT will not.

(5) VARCHAR uses 1-3 bytes to store the length, but TEXT does not.

6) CHAR is a fixed-length string and must be defined by a size modifier in parentheses. The size modifier ranges from 0 to 255. Values greater than the specified length are truncated, while values smaller than the specified length are filled with spaces.

For example, the user name for storing login users is VARCHAR (20 ):

CREATE TABLE tb1(  username VARCHAR(20) ); 

Instance:

Let's verify whether the above four fields can be successfully created:

CREATE TABLE tb1 (  username VARCHAR(20),  age TINYINT UNSIGNED,  salary FLOAT(8,2) UNSIGNED,  brithday DATE ); 

The result of creating a data table tb1 and viewing the data table structure is:

We insert records into the table tb1:INSERT tb1 VALUES ('Tom ', 22,555 5.55, '2017-10-01 ');

The execution result is as follows:

Iv. Composite Type

MySQL also supports two types of composite data, ENUM and SET, which extend the SQL specification. Although these types are technically string types, they can be considered as different data types. An ENUM type can obtain only one value from a SET, while a SET type can obtain any number of values from a SET.

(1) ENUM type

ENUM ("member1", "member2 ″,... "Member65535 ″)

The ENUM data type defines an enumeration that contains up to 65535 different members. When an ENUM column is defined, the value of this column is limited to the value declared in the column definition. If the column Declaration contains the NULL attribute, NULL is considered a valid value and the default value. If not null is declared, the first member in the list is the default value.

The ENUM type is similar to a single option because only one value can be obtained in the set. It is easy to understand when processing mutually exclusive data, such as human gender. An ENUM type field can obtain a value from the set or use a NULL value. Other input causes MySQ to insert an empty string in this field. In addition, if the case of the inserted value does not match the case of the set value, MySQL will automatically convert the case of the inserted value to a value consistent with the upper and lower case of the set.

The ENUM type can be stored as numbers in the system and indexed from 1. An ENUM type can contain a maximum of 65536 elements, one of which is retained by MySQL and used to store the current error value as a valid input. Any other input will fail. This means that the location of the error record can be easily found by searching for rows that contain null strings or corresponding numeric indexes of 0.

(2) SET Type

SET ("member", "member2 ″,... "Member64 ″)

The SET Data Type provides a method to specify zero or multiple values in a SET of predefined values, which can contain up to 64 members. The selection of values is limited to the values declared in the column definition.

The SET type is similar to the ENUM type but not the same. The SET type can obtain any number of values from a predefined SET. What is the same as ENUM type is that any attempt to insert a non-predefined value in the SET Type field will cause MySQL to insert an empty string. If you insert a record with both valid and invalid elements, MySQL retains valid elements to Remove invalid elements.

A set type can contain up to 64 elements. The value in the SET element is stored as a separated "bit" sequence, which indicates the elements corresponding to it. "Bit" is a simple and effective way to create an ordered element set. Besides repeated elements, the SET type cannot contain two identical elements. To find an invalid record from the SET type field, you only need to find the row that contains a null string or a binary value of 0.

Examples of composite data types will be used in future blog posts. Examples will not be given here.

V. Summary

A brief understanding of the usage, physical storage, and range of each data type. In this way, we can select a suitable data type based on the specific characteristics of the application, so that we can strive to meet the application requirements, the database performance is high at a lower storage cost.

Principles for selecting data types:

1) Select the smallest available type;

2) in terms of speed, select a fixed column to use the CHAR type;

3) to save space, select Dynamic columns and use the VARCHAR type.

High-performance database design principles:

Step 1:It is intuitive to roughly determine the data type, number, string, and time of a field.

Step 2:Determine a specific type, such as TINYINT, SMALLINT, INT, and LONGINT in the number, and select the most appropriate one (smaller usually better)

Step 3:If necessary, set the default value for the field.

Of course, index optimization is certainly essential, but it is within the scope of Optimization after the design table is completed.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.