MySQL (ii) detailed database data types

Preface

Today go to fitness, feel the body practice good or good, gossip not to say, the data types encountered in this database today all here to speak clearly, after seeing what data type, I should know, for me, the most unfamiliar should be the time type this piece. But through today's study, has already doubts. Just follow my rhythm and take this down.

---WH

First, the data type

MySQL's data types can be divided into 5 types, namely, integer type, floating-point type and fixed-point number type, date and time type, string type, binary type. Now you can look at the 5 types of familiarity, which seems to be crazy, it means that you are not familiar with, do not understand.

Note: integer types and floating-point types can be collectively referred to as numeric data types, which is not difficult to understand.

Numeric data type

Integer types: TINYINT, SMALLINT, Mediumint, INT, BIGINT

Floating-point type: float, DOUBLE

Fixed-point decimal: Decimal

Date/Time Type

Year, time, DATE, DATETIME, TIMESTAMP

String type

CHAR, VARCHAR, TEXT, ENUM, set, etc.

Binary type

BIT, BINARY, VARBINARY, BLOB

1, Integer type

　　　　　　　　　　　　　　　　

No matter what language you learn, on the basics, you should know 1 bytes = 8 bits binary number. The range of values for each type can also be known, such as tinyint occupies 1 bytes, that is, 8 bits, 2 8 square minus 1 equals 255, that is, if it represents an unsigned integer, the value range is 0~255, if it is signed, the highest bit is the symbol number, that is 2 7 square minus 1, That is, 127, the value range is -128~127, why need to reduce 1, this problem needs to consider the threshold value of the problem. And consider the critical value of the problem and need to discuss the original code complement anti-code knowledge, these degrees is not the focus of our discussion, so in this self-Baidu. Give a list of the scope for everyone to make reference.

Range of values for different integer types

　　　　　　　　　　

Choose a different type name according to your needs.

For example:

CREATE TABLE aaa (

ID INT (Ten) PRIMARY KEY,

Age INT (6)

);

In this example, the number in parentheses of int (10), int (6) represents the display width specified by the data type, specifying the number of digits in the numeric value that can be displayed. It is important to note that the display width and the range of data types are irrelevant, and the display width only indicates the maximum number of digits that MySQL may display, which is possible. Popular point is that, for example, the Age field, the display width of 6, but if you insert data greater than 6, reached 8,6666 6666, that's okay, as long as the number of digits inserted does not exceed the range of the value of the type integer, if the value of the insertion of the length of the number is 4, or 3, The number of digits is less than the specified width, the following digits will be filled with blanks, 5555 inserted in the Age field, the remaining is "5555" followed by a space. Also, there is no limit to the width of the display, you write 100 degrees, but when inserting data, the actual control is the value range of the data type. If you do not write the display width, you will use the system default, for example, the default display width of int is 11, look at the table above, the highest can represent the value of 10-bit size, but note that signed, that is, negative numbers, the sign bit also occupies a bit.

2. Floating-point type and fixed-point number type

　　　　　　　　　　　　

2.1 Explain the meaning of the m,d:

M: The total number of digits of the value. Popular point, is to see how many numbers, for example, 5.6789,m is 5

D: You can keep several after the decimal point. Like the 5.6789 above, D is 4. This is just an example of what m,d is, actually m,d first, and then controlling the value instead of a more numerical value to determine m,d.

Not only Mecimal have m,d these two parameters, FLOAT and DOUBLE degrees have, see example below

Like what:

CREATE TABLE tmp (

x FLOAT (3,1),

Y DOUBLE (5,3),

Z DECIMAL (5,4)

);

Suppose x inserts a value of: 5.69,56.78,5.438,349.2 (note: Experiment gives x this field to insert the value, may experiment three times, do not mistake the value of x as 5.69,y 56.78, etc.)

In fact, the value stored in the database is: 5.7,56.9,5.3,349.2 this error

Analysis: x m for 3,d is 1, then the decimal point must be accounted for a number, even if there is no value, it will be filled with zero, so that the maximum number of digits can only be 2, here to remember to first according to the value of D, to calculate the maximum number of digits can be.

It's easy to analyze X, Y and Z,

The value on the Y field, the integer part is up to 2 bits, and the number of digits after the decimal point is 3 bits, which means that after the decimal point exceeds 3 bits, it is rounded.

The value on the Z field, the integer part can only be one, the number of digits after the decimal point is 4 bits, if less than 4 bits, it will be supplemented with 0. For example, insert 1.56, the database is stored in 1.5600, such as inserting 25.46, this will be an error, because the integer part can only be one, the number of digits after the decimal point has accounted for 4 bits. We need to figure this out.

2.2, FLOAT, DOUBLE, decimal, the difference between the three.

are used to denote what we mean by the number of decimals, which is the floating-point, but the three kinds of precision are different, that is, the digits shown later are different,

Difference One:

Float shows that the decimal point at the back is about 40,

Double can show is more than 300, not a level,

Decimal the number of digits that can be displayed after the decimal point is similar to double.

Difference Two:

float and double do not specify the precision, that is, do not (M,D), the default will be based on the actual precision, that is, how much you write, and decimal if you do not specify the precision by default (10,0), that is, if you do not specify the precision, Insert the value 56.89, which is stored in the database is 57. Therefore, when using decimal, the precision is specified, and the use of float and double is not necessary.

Difference Three:

The advantage of floating-point relative and fixed-point numbers (decimal) is that floating-point numbers can represent a larger range of data in the case of certain lengths, but the disadvantage is that they cause accuracy problems.

2.3. When to use float, DOUBLE, DECIMAL

When it comes to high precision, such as currency and scientific data, the type of decimal is better. Other times, look at the size of the data you want to store, and generally use double. And in the use of floating-point numbers need to be aware of, as far as possible to avoid the comparison of floating-point numbers, such as add, subtract, who big who small, such operations, will cause the lack of precision. It is believed that in some programming languages, the problem of loss of float accuracy has been encountered.

　　　　

3. Date and Time type

　　　　　　　　　　　　

Now some things do not understand that it is okay, there is probably an understanding of the first, the next one to explain.

3.1. Year

3.1.1, focus on his storage range, 1901~2155. There are two ways to insert this number, one is to use a string to represent the inserted year value, and the other is to use a number to represent the year value, where the string can be inserted in single and double quotes, no difference, unlike some programming languages, a single character must be in single quotes, and multiple characters with double quotes. In MySQL, a single double quotation mark indicates a character.

Example:

CREATE TABLE tmp (

Y year

);

Insert data into table: INSERT INTO TMP VALUES (2010), (' 2010 '), ("2010");

Querying table data: SELECT * from TMP;

Can see three records inserted into the Database TMP table. Note: Inserting data and querying data here has not been learned, if you do not know, you can temporarily skip, directly see the conclusion.

　　　　　　　　　　　　　　　　　　　　　　　　

If you insert an out-of-range value into the table, 2166 will give an error

　　　　　　　　　　　　　　　　　　　　　　　

3.1.2, when inserting a full year, is the same as the effect of a string and a number representing year values, but it is different when you omit year values.

Example one: Take the TMP table above. Insert the year value represented by a string into the table, ' 0 ', ' 00 ', ' 77 ', ' 10 '

Delete data from table: Delete from TMP;

Insert data into table: INSERT INTO TMP VALUES (' 0 '), (' 00 '), (' 77 '), (' 10 ');

　　　　　　　　　　　　　　　　　　　　　　　

Results:

The inserted character ' 0 ', ' 00 ' became 2000

The inserted character ' 77 ' becomes 1977

The inserted character ' 10 ' becomes 2010

Example two: inserting a 2-digit number to the Y field in the TMP table represents the year value, 0,00,78,11

Delete data from table: Delete from TMP;

Insert data into table: INSERT INTO TMP VALUES (0), (00), (77), (11);

　　　　　　　　　　　　　　　　　　　　　　　

Results:

The inserted number 0, 00 becomes 0000.

The inserted number 77 becomes 1977.

The inserted number 11 becomes 2011.

　　　　　　　　

Conclusion: When writing a year is omitted,

1. The difference between a character representation and a number is 0. If the character is 0 or character 00, 2000 is generated in the database and 00 is generated if the number is 0 or 0000.

2, in no more than 70, that is, less than 70, the degree will generate more than 2000 years, that is, if 69, then generate 2069. If 70 or more contains 70, it will become more than 1970. Like 70, it becomes 1970. That is, the year value of the 00-69 range is converted to 2000~2069. 70-99 the year value of the range is converted to 1970~1999

3, generally if we want to use, also use the full name, it is not easy to confuse, but to know that there are these characteristics

3.2. Time

Format: HH:MM:SS HH means hours, MM for minutes, SS for seconds

Value range: -838:59:59 ~ 838:59:59

Explanation: The time here is not only used to represent the time of day (that is, 24 hours), it may be the time of the past or the interval between two events, in layman's terms, our usual daily time is from 0 o'clock in the morning to start the clock again, full of 24 hours, and then start again, It is also the equivalent of the current time we see the past 0 o'clock in the morning, that is, the number of hours from 0 o'clock in the morning. 7 in the morning, which is 7 hours from 0 o'clock in the morning, and so on, until the distance is 24 hours, and then start counting again. In MySQL, this time is not limited to the daily distance of 0 o'clock in the morning how long, can be the past some time distance now how long, such as yesterday morning 7 o ' clock, distance now 9 how long the morning, more than 24 hours, so. This time range is greater than the 24 hours we understand.

3.2.1, indicating time has many kinds of formats, the above HH:MM:SS is just a standard one,

1, D hh:mm:ss:d the day, the number of days. When stored in a database, D is converted to an hour save, d*24+hh

2, hh:mm, D:HH, SS: These formats can be, note that the last one, if it is a separate 2 numbers, then represents the second, such as "20" that represents the 00:00:20

3, HHMMSS: This is a string or value without spacers, such as 101112 will be interpreted as 10:11:12, but 109712 is not legal, because the minute bit more than 59. Error when storing. This should be noted when there is no colon, the rightmost two bits of the value represent seconds, and so on, such as 5523 for 00:55:23 instead of 55:23:00. So the above-mentioned SS format represents the seconds that come from the far right. If there is a colon, it starts at the left hour, for example, 55:23 is the representative 55:23:00

For example:

CREATE TABLE tmp (

T time

);

Insert Values "10:05:05", "23:23", "2 10:10", "3 02", "10", "101112", "109712"

INSERT into TMP VALUES ("10:05:05"), ("23:23"), ("2 10:10"), ("3 02"), ("10"), ("101112");

SELECT * FROM TMP;

　　　　　　　　　　　　　　　　　　　　　　

As you can see, the data is displayed as we expected.

3.2.2, use the system's functions to insert the current time.

DELETE from TMP;

INSERT into TMP VALUES (current_time), (now ());

SELECT * FROM TMP;

　　　　　　　　　　　　　　　　　　　　　

　　　　　　　　　　　　

3.3. DATE

Format: Yyyy-mm-dd YYYY represents the year MM means the month DD represents the day

Value range: 1000-01-01~9999-12-3

Data degrees using characters or numeric values can be inserted into the

Note: This is in addition to the standard format, as in time year, where there is also an ellipsis format for the years, with the same rules as in the yearly. The year value of the 00-69 range is converted to 2000~2069.  The year value of the 70-99 range is converted to 1970~1999. For example: 12-12-31 means 2012-12-31 981231 means 1998-12-31

You can also use Current_date or now () to insert the current system date.

Example: (Note that each time a table is created, the previous cousin is deleted, omitted here, using the drop table name;)

CREATE TABLE tmp (

D DATE

）;

INSERT into TMP VALUES ("1998-08-08"), (19980808), (100511), (current_date), (now ());

SELECT * FROM TMP;

　　　　　　　　　　　　　　　　　　　　　　

Note: MySQL allows the "not strict" syntax, that is, any punctuation can be used as a spacer between date parts, such as "98.11.23", "98/11/31", "[email protected]@31" Can be, you can try, But generally it is better to use the standard format and make people look comfortable.

3.4. DATETIME

Format: Yyyy-mm-dd HH:MM:SS

Value range: 1000-01-01 00:00:00~9999-12-3 23:59:59

This is not a long explanation, because this is the combination of date and time. Its various features can also be adapted here. But note that HH:MM:SS can only represent the time of day, and up to 23:59:59.

Example:

CREATE TABLE tmp (

DT DATETIME

）;

INSERT into TMP VALUES ("1998-08-08 08-08-08"), (980808080808), (Current_date ()), (now ());

SELECT * FROM TMP;

　　　　　　　　　　　　　　　　　　　　　　

Current_date () returns the date format of the current system YYYY-MM-DD so you can use this function to print the year and date degrees earlier, because it contains the information it needs for its data type

NOE () returns the date and time value of the current system, formatted as YYYY-MM-DD HH:MM:SS, so that it can be used to output a value of the DateTime type.

3.5, TIMESTAMP

Format: Yyyy-mm-dd HH:MM:SS

Value range: 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:17 UTC

Explanation: The display width is fixed at 19 characters. This is the output standard format, UTC represents the world standard time, this is basically the same as datetime, but there is one of the biggest differences we need to know.

Difference: The storage byte and the supported range are different, the most important difference is that when DateTime stores date data, it is stored in the format of the actual input, that is, what is stored in the input, and what is output, regardless of the time zone, and the storage of the timestamp value is saved in UTC format. The current time zone is converted when stored, and then converted back to the current time zone, that is, when queried, the time values displayed are different depending on the current time zone. Time zone means that you are in the United States and your time on both sides of the show is not the same, you in the United States in the United States time, much faster than China, such as 8 in the morning in China, in the United States may be 8.9 o ' Night (this is just a metaphor, ready to switch time I did not see.) )

For example:

CREATE TABLE tmp (

TS TIMESTAMP

);

INSERT into TMP VALUES (now ());

SELECT * FROM TMP;

　　　　　　　　　　　　　　　　　　　　　　

Convert time zone

　　　　　　　　　　　　　　　　　　　　　　

Can see, as we think, the output time has changed, an increase of two hours, this is about what the East 10, East 8, and so on, I do not understand these differences, in short, can come to a conclusion on the line.

In other words, timestamp and datetime are almost the same, that is, a time zone difference. Timestamp is also called a time stamp. When we meet it, we should know what it is.

4. String type

Char, Carchar, Tinytext, TEXT, Mediumtext, Longtext, ENUM, SET

　　　　　　　　　　　　　　　　　　　　

4.1, char and varchar

CHAR (m): As a fixed-length string, the fixed-length meaning is the value of M, then the value of the M is the value of its actual storage space, even if the data inserted less than M-bit, the storage space is still large, extra space to complement. At output, the space will be deleted without output. M maximum is 255, such as char (4), if you insert ABC, the stored value is ' abc ' after a space, output or ' ABC ', storage space or 4 bytes. M Max is 255

VARCHAR (M): variable length string, in contrast to char, depending on the actual size value to determine the size of the storage space, such as VARCHAR (4), insert ' AB ', the storage space is 3 bytes, look at the above image will know that VARCHAR will be more than one byte to store the length, M Max is 65535.

Note: The string is not the same as the value type, M is how large, the maximum number of characters can be inserted, more than M, will be error

Example:

CREATE TABLE tmp (

CH CHAR (4),

VCH VARCHAR (4);

）;

INSERT into TMP VALUES (' asdf ', ' ASDFG ');

Result Error:

　　　　　　　　　　　　　　　　　　　　　　

INSERT into TMP VALUES (' AB ', ' ab ');

SELECT concat (' (', ', ch, ') '), Concat (' (', ', VCH, ') ' from the tmp;//of the meaning of this sentence (the result is wrapped to observe the output of the character length)

　　　　　　　　　　　　　　　　　　　　　　　　

Description, Char is not able to store space characters, while varchar can.

　　　　　　

4.2. TEXT

Four kinds of text: Tinytext, text, Mediumtext and Longtext

tinytext:255 characters

text:65535 characters

mediuntext:16777215 characters

longtext:4294967295, about 4GB characters.

In other words, for example, if we want to save a novel, we need to use one of the above four types to store it, choosing Mediuntext or Longtext almost.

4.3. ENUM

Enum, Format: Field name enum (' Value 1 ', ' Value 2 ', ' Value 3 ' ..., ' value n '); N up to 65535

For example:

CREATE TABLE tmp (

Enm ENUM (' first ', ' second ', ' third ');

）;

Explanation: The data type of the Enm field is enum and enum type, then the value inserted in the field can only be the values in the enumeration, cannot insert the value, otherwise the error

INSERT into TMP VALUES (' first '), (' first ');

SELECT * FROM TMP;

　　　　　　　　　　　　　　　　　　　　　　　　

MySQL is case insensitive.

INSERT into TMP VALUES (' four ');

Error:

　　　　　　　　　　　　　　　　　　　　　　　　

Example two:

CREATE TABLE tmp (

Soc INT,

Level ENUM (' excellent ', ' good ', ' bad ')

）；

INSERT into TMP VALUES (70,2), (90,1), (55,3);

SELECT * FROM TMP;

　　　　　　　　　　　　　　　　　　　　　　　　

Use the index value, or you can select the value from the enumeration, starting with 1, not 0, note this

Summary: The enum type is used to limit the range of values on a field, only the value we specify.

4.4. SET

Format: Field name SET (' value 1 ', ' Value 2 ', ' Value 3 ', ' Value 4 ' ..., ' value n ') n Max 64

Use examples to illustrate the features of this set

CREATE TABLE tmp (

S SET (' A ', ' B ', ' C ', ' d ');

);

INSERT into TMP VALUES (' A '), (' A,b,a '), (' c,a,d ');

SELECT * FROM TMP;

　　　　　　　　　　　　　　　　　　　　　　　　

A becomes a

A,b,a into a A, b

C,a,d into A,c,d.

Conclusion:

1, insert the value in the Set field if there are duplicates, the duplicate values are automatically deleted

2. The values in the Insert Set field are sorted in order, and the arrangement rules are prioritized by the values in set

INSERT into TMP VALUES (' a,x,b ');

Error:

　　　　　　　　　　　　　　　　　　　　　　　　

Conclusion:

3. If you insert a value that is not part of the set, you will get an error

The set features the three points mentioned above.

5. Binary type

Used to store binary numbers, which is 01010. There are bit, BINARY, VARBINARY, Tinyblob, BLOB, Mediunblob, and Longblob

　　　　　　　　　　　　　　　　　　　　　　　

5.1. Bit type

Bit field type, M represents the number of bits inserted in the value, the maximum is 64 bits, the default value is 1, if the insertion value is less than M bit, the left side of the value is filled with 0,

Example:

CREATE TABLE tmp (

b BIT (4)

）；

Explanation: 4-bit binary, which is the ability to save values between 0 and 15.

INSERT into TMP VALUES (2), (10), (15);

SELECT BIN (b+0) from TMP;

　　　　　　　　　　　　　　　　　　　　　　　　　

Explanation: We will 2,10,15 these three 10 binary into the data table, the field is a bit type, so the table is stored in binary number, but it is displayed, to first convert the binary number to the corresponding number of values, that is, through the b+0, and then in the bin () function to convert the number to binary. We can try not to use the bin () function, only with b+0, to see what the output

SELECT b+0 from TMP;

　　　　　　　　　　　　　　　　　　　　　　　　　　

5.2. Binary and varbinary types

Format: Field BINARY (m) or varbinary (m)

These two are similar to char and varchar,

BINARY: The length is fixed, after the specified length, the most insufficient, will be on their right to fill "" "to achieve the specified length,

VARBINARY: Length is variable, after the length of the length can be between 0 to the maximum, for example, the specified field data type is VARBINARY (20), if the length of the inserted value is only 10, then the actual storage space is 10 plus 1, that is, the actual occupied space is the actual length of the string plus 1 .

Said so much, but still do not understand the usefulness of these two, now to tell you, we say that MySQL is not sensitive to case, but these two data types are case-sensitive, because they are binary to save data, such as a and a, two binary is not the same. So there are times when we need to be case sensitive and that type is used.

Note: The lengths of these two types are calculated as byte lengths, a character equal to 2 bytes, such as binary (4), which can hold 4 bytes of length, which means that only 2 characters can be stored. You can save 4 letters. Do not take this length as the length of the bits, said binary string means that the binary is used to store, but its length constraint is the length of bytes.

Example:

CREATE TABLE tmp (

b BINARY (10)

）;

INSERT into TMP VALUES (' a ');

SELECT * FROM tmp WHERE b= ' A ';

　　　　　　　　　　　　　　　　　　　　　　　　　　

Explanation: Storing a lowercase A in the TMP table, and then using the uppercase a lookup table to see if a can be found, the result is not found, it validates our statement above, has a case-sensitive function.

Summary: Binary and varbinary's main role is to be used to distinguish between the case, the other does not work, but the use of attention to limit its size is the number of bytes, rather than bits, it stores the format is binary to store. Don't confuse the two.

5.3. Blob type

is a binary large object, Tinyblob (32KB), BLOB (64KB), Mediumblob (16M), and Longblob (4GB). The general store is some images, audio files.

Ii. How to select a data type

1, integers and floating-point numbers

If the fractional part is not required, the integer is used to hold the data, and the appropriate integer type is selected based on the size of the integer, if a fractional portion is required, the float type is used, float and double are in the floating-point type, and double is selected if a higher precision is required. Decide what to choose according to your own needs.

2. Floating point and fixed point number

Floating-point float, double relative to fixed-point number decimal has the advantage that, in the case of a certain length of time, floating-point numbers can represent a larger range of data, but floating-point numbers are prone to error, so in high precision, it is recommended to use decimal, such as money, such things, It is reasonable to use decimal, and note that floating-point numbers are prone to problems in addition and subtraction operations. If you do a numeric comparison, it is also advisable to use decimal

3. Date and Time type

Can look at the above in detail when the diagram, according to various formats, choose their own data type, note the difference between timestamp and datetime, one is related to the time zone, an unrelated, no big difference.

4. Character and choice between char and varchar

Difference:

Char is a fixed-length character, and varchar is a variable-length character. Char automatically deletes trailing spaces that are inserted into the data, and varchar does not.

Char is fixed length, processing speed is faster than varchar, the drawback is obvious, waste storage space, so the storage is not small, but the speed of the requirements can use the char type, and vice versa with varchar.

5. Enum and set

An enum can only take a single value, that is, pick one of the values from the enumeration type, but set can take multiple values,

Enum can hold up to 65,535 members, set can only be 65

An empty string can also be stored in set,

To store a person's favorite, it is best to use set type, in fact, the most important thing is to see the specific situation in the selection of the most appropriate

6. Blob and text

BLOBs are binary strings, text is non-binary strings, both can hold large volumes of information, blobs mainly store pictures, audio information, and text can only store plain text files. Clarify the purpose of the two

7. Binary and varbinary

The difference between the two is similar to char and varchar, binary is fixed length, varbinary is variable, and the two functions are to be case-sensitive, note that these two are byte strings.

　　

　　　　　

MySQL (ii) detailed database data types