MySQL learning 6:mysql basic data types

Data types are data characteristics of columns, stored procedure parameters, expressions, and local variables, which determine how data is stored and represent different information

Type. The data types commonly used in MySQL include: numeric types, date and time types, string types, and so on.

A numeric type

MySQL supports all numeric types in standard SQL, including strict data types (INTEGER, SMALLINT, DECIMAL,

numberic), and an approximate numeric data type (FLOAT, REAL, DOUBLE, Presision), and expand on that basis. increased after expansion

tinyint, Mediumint, bigint are added to these 3 different lengths of shaping, and the bit type is added to hold the bit data.

(1) integral type

The 5 major integer types supported in MySQL are tinyint, SMALLINT, mediumint, int, and bigint. These types in a very large range

Are the same, only the sizes of the values they store are not the same.

MySQL expands the SQL standard in the form of an optional display width indicator, so that when retrieving a value from a database, this value can be

The length is extended to the specified lengths. For example, specifying that a field is of type int (6) guarantees that a value that contains fewer than 6 digits is retrieved from the database

can be filled automatically with spaces. It is important to note that using a width indicator does not affect the size of the field and the range of values that it can store. in the event

We need to store a number in a field that is beyond the permitted range, and MySQL will truncate it to the one end of the allowable range and then store it. And also

One of the more special areas is that MySQL will automatically change to 0 before the non-compliant value is inserted into the table.

The following table shows the storage and scope of each integer type that is required:

The above definitions are signed, of course, can also be added unsigned keyword, defined as unsigned type, then the corresponding value range

Will be turned over, for example: TINYINT unsigned value range is 0~255.

For example, we store people's age, that is, using tinyint UNSIGNED:

CREATE tabble tb1 (age    TINYINT UNSIGNED,);

(2) floating-point type

The three floating-point types supported by MySQL are the float, double, and decimal types. The float value type is used to represent a single-precision floating point value

A double numeric type is used to represent a dual-precision floating-point value. As with integers, these types also have additional parameters: a display width indicator and a

Decimal point indicator. For example, the statement FLOAT (7,3) specifies that the value displayed will not exceed 7 digits, followed by a 3-digit number after the decimal point. For the following decimal point

When the number of digits exceeds the allowable range, MySQL automatically rounds it to the nearest value, and then inserts it.

The decimal data type is used in calculations that require very high precision, which allows you to specify the precision and count method of a numeric value as a selection parameter. Precision

This refers to the total number of valid digits saved for this value, while the Count method represents the number of digits after the decimal point. For example, the statement decimal (7,3) prescribes

The stored value does not exceed 7 digits, and no more than 3 digits after the decimal point.

The following table shows their storage scope:

I built a table in MySQL with a column named float (5, 3), and the following tests were done:

1) Insert 123.45678, the last query results are 99.999;

2) Insert 123.456, the last query result is 99.999;

3) Insert 12.34567, the last query result is 12.346;

Therefore, in the use of floating-point type, but also to pay attention to the trap, to insert the actual results in the database will prevail.

For example, our store worker's salary is to use float (8,2) UNSIGNED:

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

2nd period Time Type

MySQL has 5 different data types to choose from when working with date and time type values. They can be divided into simple dates, time classes

Type, and mixed date and time types. Depending on the accuracy required, subtypes are available in each classification, and MySQL has built-in functionality to

The diversified input format becomes a standard format.

The following table explains the five types of date and time:

1) MySQL stores a simple date value with the date and year type, and the time value is used to store it. These types can be described as strings or not

A sequence of integers with delimiters. If the description is a string, the value of the date type should be separated by a hyphen, and the value of the time type should make the

Separate with a colon as a delimiter. Note that the time type value without the colon delimiter will be understood by MySQL as the duration, not

Time stamp.

2) In addition to the date and time data types, MySQL supports both the dateyime and timestamp hybrid types. They can put the date and time

stored as a single value. These two types are typically used to automatically store timestamps that contain the current date and time, and to perform a large number of database

And the need to establish an audit trail for debugging and auditing purposes that plays a good role in the application. If we don't have a field for the timestamp type

Explicitly assigned, or is assigned a null value. MySQL will automatically populate it with the current date and time of the system.

For example, we store added user's birth date is used:

CREATE tabble tb1 (    brithday DATE);

three string types

MySQL provides 6 basic string types, namely char, VARCHAR, Tinytest, TEXT, Mediumtext,

Longtext six types of strings. can store ranges from simple one character to huge blocks of text or binary string data.

The subscript explains six basic string types:

1) CHAR (n) and varchar (n) denote the number of characters in parentheses, not the number of bytes, so when using Chinese (UTF8)

Taste You can insert M Chinese, but it actually consumes m*3 bytes.

2) The biggest difference between char and varchar is that Char will occupy N-character space regardless of the actual value, and varchar will only

accounted for the actual character should occupy +1 of the space, and the actual space +1<=n.

3) after the N setting of char and varchar is exceeded, the string is truncated.

4) Char will truncate trailing spaces when stored, varchar and text will not.

5) varchar uses 1-3 bytes to store the length, text does not.

6) The char type is used for fixed-length strings and must be defined within parentheses with a size modifier. This size modifier ranges from 0-

255. a value larger than the specified length will be truncated, and a value smaller than the specified length will be filled with a space.

For example, we store the user name of the logged-in user with varchar (20):

CREATE TABLE tb1 (    username VARCHAR (20));

Example:

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

CREATE TABLE tb1 (    username VARCHAR), 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 a record into the data table tb1:

INSERT tb1 VALUES (' Tom ', 22,5555.55, ' 1994-10-01 ');

The results of the execution are shown as:

Four composite types

MySQL also supports two composite data types, enum and set, which extend the SQL specification. Although these types are technically string types,

is a data type that can be considered different. An enum type allows only one value to be obtained from a collection, whereas a set type allows a

Any number of values.

(1) enum type ENUM ("Member1″," Member2″, ... "Member65535″)

The enum data type defines an enumeration with a maximum of 65,535 distinct members. When a column with an enum is defined, the value of the column is limited to

The value declared in the column definition. If the column declaration contains a null attribute, then NULL is considered to be a valid value and is the default value. If the not is declared

NULL, the first member of the list is the default value.

The enum type is somewhat similar to a single option because it allows only one value to be obtained in the collection. It is easy to understand when dealing with data that is detached from one another, such as

The gender of the human race. The Enum type field can take a value from the collection or use a null value, otherwise the input will cause the MYSQ to be inserted in this field

into an empty string. In addition, if the casing of the inserted value does not match the case of the value in the collection, MySQL automatically converts the casing of the inserted value to the

The values in the collection that match the case.

The enum type can be stored as a number inside the system, and it is indexed with numbers starting from 1. An enum type can contain a maximum of 65,536 meta-

element, where one of the elements is retained by MySQL, and the value used to store the wrong present is the legal input, and any other input will fail. This means that by searching

A line that contains an empty string or a corresponding numeric index of 0 can easily find the location of the error record.

(2) set type

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

The Set data type provides a way to specify 0 or more values in a predefined set of values, which includes a maximum of 64 members. Selection limits for values

The value declared in the column definition.

The set type is similar but not the same as the enum type. A set type can take any number of values from a predefined collection. And with the enum type

The same is any attempt to insert a non-predefined value in the Set Type field causes MySQL to insert an empty string. If you insert one that has a valid meta-

Elements, MySQL will retain the legal elements, remove the illegal elements.

A SET type can contain up to 64 elements. The values in the set element are stored as a separate "bit" sequence, which represents the corresponding

The element. A bit is a simple and efficient way to create a collection of ordered elements. And it also removes duplicate elements, so the set type cannot be wrapped

contains two identical elements. To find illegal records from the set Type field, simply look for rows that contain an empty string or binary value of 0.

Examples of composite data types we will use later in the post, no longer examples.

Five Summary

There is an overview of the use of each data type, physical storage, presentation scope, and so on. In the face of specific applications, it is possible to

To choose the right data type, so that we can strive to meet the application on the basis of a small storage cost for higher database

Yes.

The principle of choosing a data type:

1) Select the smallest available type;

2) from the aspect of speed, select fixed column using char type;

3) From space-saving considerations, select dynamic columns, using the varchar type.

High Performance Database design principles:

The first step: roughly determine the field data type, numbers, strings, time, etc., more intuitive.

The second step: determine the specific type, for example: the number has tinyint, SMALLINT, INT, Longint, etc., choose the most appropriate one (more

Small usually better)

Step three: Set a default value for the field, if necessary.

Of course, index optimization is certainly necessary, but this is the scope of optimization after the completion of the design table.

MySQL learning 6:mysql basic data types