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

Preface

When I went to the gym today, I felt that it was good to practice my body well. Without much gossip, I explained all the data types encountered in this database here today. We should know what data types we see in the future. For me, the most unfamiliar thing should be the time type. But through today's study, the confusion has been solved. Let's follow my rhythm and take this down.

-WH

I. data type

MySQL data types can be divided into five types, namely, integer type, floating-point type and fixed-point type, date and time type, string type, and binary type. Now you can take a look at your familiarity with these five types, and which one looks confused, it shows which one you are not familiar with and do not understand.

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

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, you should know 1 byte = 8-bit binary number in the basic aspect. The value range of each type can be known, for example, TINYINT occupies 1 byte, that is, 8 bits, 2 to the power of 8 minus 1 equals 255. that is to say, if it represents an unsigned integer, the value range is 0255. if it is signed, the highest bit is the sign bit, that is, 2 to the seventh power minus 1, that is, 127. the value range is-128 to 127, why do you need to subtract 1? This problem needs to consider the problem of critical value. Considering the critical value problem, it is necessary to discuss the knowledge of the original code complement and anti-code, these degrees are not the focus of our discussion, so here on their own Baidu. Give a range table for your reference.

The range of values for different integer types

Choose different type names according to your needs.

For example:

CREATE TABLE aaa (id INT (10) PRIMARY KEY, age INT (6))

In this example, the numbers in parentheses INT (10) and INT (6) represent the display width specified by the data type and specify the number of numbers that can be displayed. Note here: the display width has nothing to do with the value range of the data type, the display width only indicates the maximum number of digits that MySQL can display, note that it is possible. The popular point is, for example, this age field shows a width of 6, but if the data you insert is greater than 6, reaching 8p6666 6666, it doesn't matter, as long as the number of digits inserted does not exceed the range of integers of this type, it is fine. If the length of the inserted value is 4, or 3, and the number of digits is less than the specified width, the following digits will be filled in by blanks, and 5555 will be inserted into the age field. What is saved is filled in with a space after "5555". Another thing is that there is no limit to the width of the display, and it's okay for you to write 100 degrees, but when inserting data, it's the range of values that actually controls 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. If you look at the table above, the highest value can represent a value of 10 digits, but it should be noted that when there is a signed, that is, a negative number, the symbol bit also occupies a bit.

2. Floating point number type and fixed point number type

2.1 explain the meaning of Mpower D:

M: the total number of digits of the value. To put it more colloquially, it depends on how many numbers there are. For example, 5.6789 M is 5.

D: how many places can be retained after the decimal point? For example, 5.6789 above, D is 4. This is just an example to illustrate what MMagneD is. In fact, there is MMagneD first, and then to control the numerical value, rather than having more numerical value to determine MMagneD.

It is not only MECIMAL that has two parameters, such as FLOAT D and DOUBLE degree, see the following example

For example:

CREATE TABLE tmp (

X FLOAT (3par 1)

Y DOUBLE (5pas 3)

Z DECIMAL (5pas 4)

)

Suppose the inserted value of x is: 5.69, 56.78, 5.438349.2 (Note: the value inserted into the field of x may have been tested three times, do not mistake it for 5.69, 56.78, etc.)

In fact, the value stored in the database is: 5.7, 56.9, 5.3349.2

Analysis: the M of x is 3 and D is 1, then the decimal point must be one digit, even if there is no value, it will be filled with 0, so the integer can only be 2 digits at most. Here, we must remember to calculate the maximum number of integer digits according to the value of D.

It will be simple by analyzing XBI y and z.

For the value on the y field, the integer part is at most 2 digits, and the number of digits after the decimal point is up to 3 digits, that is, more than 3 digits after the decimal point, it is rounded.

For the value on the z field, the integer part can only be one digit at most, and the number of digits after the decimal point is at most 4 digits. If it is less than 4 digits, it will be supplemented with 0. For example, insert 1.56, the storage in the database is 1.5600, for example, insert 25.46, this will report an error, because the integer part can only be one digit, and the number of digits after the decimal point already accounts for 4 digits. We need to figure it out here.

2.2The differences among FLOAT, DOUBLE and DECIMAL.

They are all used to represent what we call decimals, that is, floating point numbers, but the precision of the three is different, that is, the number of digits shown later is not the same.

Difference one:

FLOAT shows that the following decimal place is about more than 40 places.

What DOUBLE can show is more than 300 digits, not at one level.

The number of digits that can be displayed after the decimal point DECIMAL is similar to that of DOUBLE.

Difference two:

FLOAT and DOUBLE do not specify precision, that is, do not use (MMagol D), the default will be according to the actual accuracy, that is, how much you write, while DECIMAL if you do not specify the accuracy defaults to (10dint 0), that is, if you do not specify the precision, insert the value 56.89, and store 57 in the database. Therefore, precision is generally specified when using DECIMAL, but not when using FLOAT and DOUBLE.

Difference 3:

The advantage of floating point number compared with fixed point number (DECIMAL) is that floating point number can represent a larger data range when the length is fixed, but the disadvantage is that it will cause accuracy problems.

When to use FLOAT, DOUBLE, DECIMAL

When the precision requirement is high, such as currency, scientific data, etc., it is better to use the type of DECIMAL. Other times, depending on the size of the data you want to store, you usually use DOUBLE. And when using floating-point numbers, we should pay attention to avoid comparing floating-point numbers as far as possible, such as adding and subtracting, who is big and who is small, which will lead to lack of accuracy. It is believed that the problem of loss of float precision has been encountered in some programming languages.

3. Date and time type

It doesn't matter if I don't understand some things now. I probably have an understanding first, and then I'll explain them one by one.

3.1 、 YEAR

3.1.1, focus on his storage range, 1901 to 2155. When inserting this value, there are two ways, one is to use a string to represent the inserted YEAR value, the other is to use a number to represent the YEAR value, in which the string can be inserted in single quotation marks and double quotation marks, no difference, unlike some programming languages, a single character must use single quotation marks, multiple characters must use double quotation marks, in MySQL, single and double quotation marks degree to represent characters.

Example:

CREATE TABLE tmp (

Y YEAR

);

Insert data into the table: INSERT INTO tmp VALUES (2010), ('2010'), ("2010")

Query data in the table: SELECT * FROM tmp

Can see that three records are inserted into the database tmp table. Note: here insert data and query data operation has not been learned, if you do not know, you can skip for a while, directly look at the conclusion.

If you insert out-of-range values into the table, 2166 will report an error

3.1.2. When inserting a full year, the effect of using a string to represent the year value is the same as using a number to represent the year value, but when the year value is omitted, the two ways are different.

Example 1: take the tmp table above. Insert into the table the value of year represented by a string,'0', '00',' 77', '10'

Delete data from the table: DELETE FROM tmp

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

Results:

The inserted characters'0' and '00' become 2000

The inserted character '77' becomes 1977.

The inserted character '10' becomes 2010

Example 2:

Insert a 2-digit number into the y field in the tmp table to indicate the year value.

Delete data from the table: DELETE FROM tmp

Insert data into the 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 omitting the year,

1. The difference between characters and numbers lies in 0. If it is a character 0 or 00, it generates 2000 in the database, and if it is a number 0 or 00, it generates 0000.

2. If it is not more than 70, that is, less than 70, the degree will generate more than 2000 years, that is, if it is 69, it will generate 2069. If it is more than 70, including 70, it will become more than 1970. For example, 70 will become 1970. That is, the annual value in the range of 00-69 is converted to 2000-2069. The annual value in the range of 70-99 is converted to 1970-1999

3. Generally speaking, if we want to use it, we will use the full name, which is not easy to be confused, but we need to know that there are these features.

3.2 、 TIME

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

Value range:-838 59. 59 to 838.

Explanation: the time here can be used not only to represent the time of the day (that is, 24 hours), but also the time in the past of something or the interval between two events. In popular terms, our usual daily time is to start again from 0: 00 in the morning, the full 24 hours, and then start all over again, which is equivalent to the time that we see now is past 0: 00 in the morning. That's how many hours have passed since 0: 00 in the morning. 7: 00 in the morning, that is, 7 hours from 0: 00 in the morning, and so on, until the distance is 24 hours, and then start the calculation again. In MySQL, this TIME is not limited to how long it is from 0am every day, it can be how long it is from a certain time in the past, such as 7am yesterday, 9.00am now, it is more than 24 hours, so. The range of values of this TIME is larger than what we understand as 24 hours.

3.2.1. There are many formats for TIME, and the HH:MM:SS above is just one of the standard formats.

1. D HH:MM:SS: d represents days and days. When stored in the database, D is converted to hourly save, D*24+HH

2. HH:MM, D:HH, SS: these formats are OK. Pay attention to the last one. If there are two numbers alone, it represents seconds. For example, "20" represents 00:00:20.

3. HHMMSS: this is an undelimited string or numeric value. For example, 101112 will be understood as 10:11:12, but 109712 is illegal because it exceeds 59 in minutes. An error will be reported when storing. Note that when there is no colon, the rightmost two digits represent seconds, and so on, for example, 5523 means 00:55:23 instead of 55:23:00. So the above SS format represents seconds, and that's how it comes from, from the far right. If there is a colon, it starts on the left. For example, 55:23 means 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;

It can be seen that the data are displayed as we expected.

3.2.2. Insert the current time using the function of the system.

DELETE FROM tmp

INSERT INTO tmp VALUES (CURRENT_TIME), (NOW ())

SELECT * FROM tmp

3.3 、 DATE

Format: YYYY-MM-DD YYYY for year MM for month DD for day

Value range: 1000-01-01 / 01 / 99 / 12 / 3

Data degrees using characters or numeric values can be inserted

Note: in addition to the standard format, this is the same as TIME YEAR, there is also an omitted format for the year, and the rules are the same as in YEAR. The annual value in the range of 00-69 is converted to 2000-2069. The annual value in the range of 70-99 is converted to 1970-1999. For example: 12-12-31 for 2012-12-31 981231 for 1998-12-31

You can also insert the current system date using CURRENT_DATE or NOW ().

Example: (note that the previous old table is deleted before each table is created, which is omitted here and uses the DROP TABLE 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 "lax" syntax, that is, any punctuation degree can be used as a spacer between date parts, such as "98.11.23", "98-11-31", "9811" 31 ", you can try, but generally use the standard format is better, make people look comfortable.

3.4 、 DATETIME

Format: YYYY-MM-DD HH:MM:SS

Value range: 1000-01-01 0012-3 23:59:59

There is no need to explain this, because this is a combination of DATE and TIME. Its various characteristics can also be suitable here. But note that HH:MM:SS can only indicate the time of day, that is, until 23:59:59 at most.

Example:

CREATE TABLE tmp (

Dt DATETIME

)

INSERT INTO tmp VALUES ("1998-08-0808-08-08"), (98080808080808), (CURRENT_DATE ()), (NOW ())

SELECT * FROM tmp

Note: MySQL allows "lax" syntax, that is, any punctuation degree can be used as a spacer between date parts, such as "98.11.23", "98-11-31", "9811" 31 ", you can try, but generally use the standard format is better, make people look comfortable.

3.4 、 DATETIME

Format: YYYY-MM-DD HH:MM:SS

Value range: 1000-01-01 0012-3 23:59:59

There is no need to explain this, because this is a combination of DATE and TIME. Its various characteristics can also be suitable here. But note that HH:MM:SS can only indicate the time of day, that is, until 23:59:59 at most.

Example:

CREATE TABLE tmp (

Dt DATETIME

)

INSERT INTO tmp VALUES ("1998-08-0808-08-08"), (98080808080808), (CURRENT_DATE ()), (NOW ())

SELECT * FROM tmp

CURRENT_DATE () returns the date format YYYY-MM-DD of the current system, so you can use this function to print YEAR and date degrees before, because it contains the information required by its data type.

NOE () returns the date and time values of the current system in the format YYYY-MM-DD HH:MM:SS, so you can use it here to output values of type DATETIME.

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 world standard time, which is basically the same as DATETIME, but there is one big difference that we need to know.

Difference: storage bytes and supported range are different, the most important difference is that when DATETIME stores date data, it is stored according to the actual input format, that is, what is input and what is output, regardless of the time zone, while the timestamp value is stored in UTC format, the current time zone will be converted when it is stored, and the retrieval time will be converted back to the current time zone, that is, when querying, it will be based on the current time zone. The time values displayed are different. The time zone means that your time in the United States is different from that in China. You have time in the United States, which is much faster than that in China. For example, it is only 8: 00 in the morning in China. In the United States, it may be 8: 9 p.m. (this is just an analogy. I didn't see it when I was going to change the time.)

For example:

CREATE TABLE tmp (

Ts TIMESTAMP

)

INSERT INTO tmp VALUES (NOW ())

SELECT * FROM tmp

Convert time zone

As we can see, as we think, the output time has changed, increased by two hours, this is about what East 10, East 8, and so on, I do not understand these jet lag, anyway, I can draw a conclusion.

In other words, TIMESTAMP and DATETIME are actually similar, just the difference in a time zone. TIMESTAMP is also called time stamp. When we meet it, we should know what it is.

4. String type

There are CHAR, CARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM, SET

4. 1, CHAR and VARCHAR

CHAR (M): for a fixed-length string, fixed length means what the value of M is, then the value of M is the value of its actual storage space. Even if the inserted data is less than M bits, the storage space is still so large that the excess space is filled with spaces. On output, the space will be deleted and not output. M has a maximum of 255m, such as char (4). If you insert abc, the stored value is' abc' followed by an extra space, and the output is still 'abc', storage still takes up 4 bytes. M up to 255

VARCHAR (M): a variable length string, contrary to CHAR, determines the size of the storage space based on the actual size value, such as VARCHAR (4). If you insert 'ab', the storage space is 3 bytes. As you can see from the figure above, VARCHAR will use an extra byte to store the length, and the maximum M is 65535.

Note: the string is different from the numeric type. The maximum number of characters that can be inserted is as large as M. If the string exceeds M, an error will be reported.

Example:

CREATE TABLE tmp (ch CHAR (4), vch VARCHAR (4);); INSERT INTO tmp VALUES ('asdf','asdfg')

An error was reported as a result:

INSERT INTO tmp VALUES ('ab','ab')

The meaning of SELECT concat ((', ch,')'), concat ((', vch,')') FROM tmp;// is used (wrap the result to observe the character length of the output)

Note that CHAR cannot store space characters, while VARCHAR can.

4.2 、 TEXT

There are four types of text: TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT

TINYTEXT:255 character

TEXT:65535 character

MEDIUNTEXT:16777215 character

LONGTEXT:4294967295, probably the character of 4GB

In other words, for example, if we want to save a novel, we need to use one of the four above to store it. Choose MEDIUNTEXT or LONGTEXT.

4.3 、 ENUM

Enumeration, format: field name ENUM ('value 1', 'value 1', 'value 2', 'value 3', 'value n'); n is up to 65535

For example:

CREATE TABLE tmp (

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

);

Explanation: the data type of the enm field is ENUM and the enumeration type, so the values inserted in the field can only be these values in the enumeration, and other values cannot be inserted, otherwise an error will be reported.

INSERT INTO tmp VALUES ('first'), (' FIRST')

SELECT * FROM tmp

MySQL is not case sensitive.

INSERT INTO tmp VALUES ('four')

Error report:

Example 2:

CREATE TABLE tmp (soc INT, level ENUM ('excellent','good','bad')); INSERT INTO tmp VALUES (70 FROM tmp 2), (90 score 1), (55 FROM tmp 3); SELECT *

Using index values, you can also choose the values in the enumeration, starting with 1, not 0. Note this.

Summary: the purpose of using the ENUM type is to limit the range of values on the field, only the values we specify.

4.4 、 SET

Format: field name SET ('value 1', 'value 2', 'value 3', 'value 4', 'value n') n up to 64

The characteristics of this SET are explained by examples.

CREATE TABLE tmp (s SET); INSERT INTO tmp VALUES ('a`a'), (' ameme bmemoria'), ('cmagentiad`); SELECT * FROM tmp

A becomes a

A _.

Cpenary a penny d becomes a pencil cpentry d.

Conclusion:

1. If there is a duplicate value inserted in the SET field, the duplicate value will be deleted automatically.

2. The values inserted into the SET field will be arranged in order, and the arrangement rule is to prioritize the values in the SET.

INSERT INTO tmp VALUES ('a _

Error report:

Conclusion:

3. If you insert a value that does not belong to SET, an error will be reported.

The characteristics of SET are the three points mentioned above.

5. Binary type

It is used to store binary numbers, that is, 01010. There are BIT, BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUNBLOB and LONGBLOB

5.1Type of BIT

Bit field type. M represents the number of digits of the inserted value. The maximum is 64 bits. The default value is 1. If the inserted value is less than the M bit, the left side of the value is filled with 0.

Example:

CREATE TABLE tmp (b BIT (4))

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

INSERT INTO tmp VALUES (2), (10), (15);

SELECT BIN (bread0) FROM tmp

Explanation: we store the three decimals in the data table, whose fields are of BIT type, so what is stored in the table is a binary number, but to display it, we must first convert the binary number to the value of the corresponding number, that is, through bimal 0, and then convert the number to binary through the BIN () function. We can try not to use the BIN () function, but just use bicon0 to see what's output.

SELECT bust 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. If the length is not enough, the right side of the length will be filled with "\ 0" to reach the specified length.

VARBINARY: the length is variable. After setting the length, the length can be between 0 and the maximum. For example, if the field data type is specified as VARBINARY (20), if the length of the inserted value is only 10, the actual storage space is 10 plus 1, that is, the actual space occupied is the actual length of the string plus 1.

Having said so much, I still don't understand the usefulness of these two. Now let's tell you that MySQL is not case-sensitive, but these two data types are case-sensitive because they use binary to store data, such as An and a, which are different. So in many cases when we need to be case-sensitive, we will use this type.

Note: the length of these two types calculates the byte length, and one character is equal to 2 bytes, such as BINARY (4), which means that it can store 4 bytes, that is, only 2 Chinese characters. You can save four letters. Don't think of this length as the length of a binary bit, saying that a binary string means that it is stored in binary, but its length constraint is byte length.

Example:

CREATE TABLE tmp (b BINARY (10)); INSERT INTO tmp VALUES ('a'); select * from tmp WHERE baked goods A'

Explanation: a lowercase an is stored in the tmp table, and then through the uppercase A look-up table to see if a can be found, but the result is not found, which verifies our above statement and has a case-sensitive function.

Summary: the main role of BINARY and VARBINARY is to be case-sensitive, the other is not useful, but be careful to limit its size is the number of bytes, not binary bits, its format is stored in binary. Don't confuse the two.

5.3Type of BLOB

Is a binary large object, TINYBLOB (32kb), BLOB (64kb), MEDIUMBLOB (16m), and LONGBLOB (4GB). Generally, some images and audio files are stored.

How to choose the data type

1. Integers and floating point numbers

If the decimal part is not needed, then use the integer to save the data, and according to the size of the integer, to select the appropriate integer type, if you need the decimal part, then use the floating-point type, floating-point type, there are float and double, if you need higher precision, choose double. Decide what to choose according to your own needs.

2. Floating point number and fixed point number

Floating-point FLOAT, DOUBLE corresponding fixed-point DECIMAL advantage is: in the case of a certain length, floating-point number can represent a larger range of data, but floating-point number is easy to produce errors, so when the accuracy is relatively high, it is recommended to use DECIMAL, such as currency, it is more reasonable to use DECIMAL, pay attention to floating-point numbers in the addition and subtraction operation is also prone to problems. If numerical comparison is made, it is also recommended to use DECIMAL

3. Date and time type

You can look at the diagram explained in detail above, choose the data type you need according to various formats, and pay attention to the difference between TIMESTAMP and DATETIME. One is related to the time zone, the other is irrelevant, and the other is not much different.

4. The characteristics and choices between CHAR and VARCHAR.

Difference:

CHAR is a fixed-length character and VARCHAR is a variable-length character. CHAR automatically removes trailing spaces from inserted data, but VARCHAR does not.

CHAR is a fixed length, the processing speed is faster than VARCHAR, the disadvantage is obvious, a waste of storage space, so the storage is not large, but the speed requirements can use the CHAR type, and vice versa.

5. ENUM and SET

ENUM can only take a single value, that is, one of the values from the enumerated type, but SET can take multiple values

ENUM can store up to 65535 members, while SET can only have 65 members.

Empty strings can also be stored in SET

To store a person's favorite, it is best to use the SET type. In fact, the most important thing is to select the most appropriate one according to the specific situation.

6. BLOB and TEXT

BLOB is a binary string and TEXT is a non-binary string, both of which can store large-capacity information. BLOB mainly stores picture and audio information, while TEXT can only store plain text files. Distinguish the use of the two

7. BINARY and VARBINARY

The difference between these two is similar to that between CHAR and VARCHAR. BINARY is of fixed length and VARBINARY is variable. The purpose of these two is to be case-sensitive. Note that these two are byte strings.

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.