Common data types for MySQL learning notes

Http://www.cnblogs.com/doit8791/archive/2012/05/11/2495319.html

A data type is a basic rule that defines what data can be stored in a column and how that data is actually stored. MySQL's common data types include:

1. string Data type : The most commonly used data type, there are two basic types of strings: fixed-length strings and variable-length strings, respectively. Fixed-length string end-of-time character, whose length is the creation table is specified, does not allow more than the specified character data, they allocate as much storage space as specified, char is a fixed-length string type. Variable long strings store variable-length text, some longer data types have the largest fixed length, while others are completely longer, regardless of which only the specified data is saved (no extra spaces are saved), and text is a variable-length string type. Variable length data types are flexible, fixed-length data types are efficient, MySQL processing fixed-length data types is much faster than variable-length columns, and MySQL does not allow indexing of variable-length columns (or variable portions of a column), which can greatly affect performance. The specific types are described in the following table:

Data type	Description
CHAR	A fixed-length string of 1~255 characters whose length must be specified at creation time, otherwise MySQL is assumed to be char (1)
VARCHAR	Variable length, up to 255 bytes, if varchar (n) is specified on creation, can store 0~n characters of variable length string
Tinytext	Same text with a maximum length of 255 bytes
Meduimtext	Same text with a maximum length of 16K
TEXT	Variable length text up to 64K maximum
Longtext	Same text with a maximum length of 4GB (plain text, usually not to 4G)
Enum	Accepts a string from a predefined collection of up to 64K strings
SET	accepts 0 or more strings of a predefined set consisting of up to 64K strings

Note: Regardless of the string data type in any form, the string value must be within quotation marks (usually a single quotation mark is better), if the numeric value is used in the calculation, it should be stored in the numeric data type column, if used as a string (such as phone number, postal code) should be saved in the String data type column.

1. Numeric data Types : Store numeric values, each with a different storage range, and the larger the range of values, the more storage space is required. All numeric types (except bit and Boolean) can be signed or unsigned, and a signed data column can store a positive or negative numeric value, which is signed by default.

Type description	Storage requirements	Range of values
tinyint[(m)]	1 bytes	Signed value: 128 to 127 (-2^7 to 2^7–1)   Unsigned value: 0 to 255 (0 to 2^8–1)
smallint[(m)]	2 bytes	Signed values:-32768 to 32767 (-2^15 to 2^15–1)   Unsigned value: 0 to 65535 (0 to 21 6–1) /p>
mediumint[(m)]	3 bytes	Signed values:-8388608 to 8388607 (-2^23 to 2^23–1)   Unsigned value: 0 to 16777215 (0 To 2^24–1)
int[(m)]	4 bytes	Signed values:-2147683648 to 2147683647 (-2^31 to 2^31-1)   Unsigned values: 0 to 4294967295 (0 to 2^32–1)
bigint[(m)]	8 bytes	Signed values: 9223372036854775808 to 9223373036854775807 (-2^63 to 2^63-1)   Unsigned value: 0 to 18446744073709551615 (0 to 2^64–1)
float[(M, d)]	4 bytes	min Non 0 value: ±1.175494351e–38
double[(M, d)]	8 bytes	min Non 0 value: ±2.2250738585072014e–308
Decimal (M, D)	M bytes (MySQL < 3.23), m+2 bytes (mysql > 3.23)	The range of values depends on M and D

MySQL provides 5 types of integers: tinyint, smallint, mediumint, int, and bigint (bytes 1, 2, 3, 4, 8), which differ in the range of values that can be represented. Integer columns can be defined as unsigned thereby disabling negative values, which makes the column range above 0.

MySQL offers three floating-point types: float, double, and decimal. Unlike integers, floating-point types cannot be unsigned, and their range of values differs from integer types, not only because they have the largest value, but also have a minimum value of 0. The minimum value provides a measure of the corresponding type precision, which is important for recording scientific data (and, of course, negative maximum and minimum values).

When you select a numeric type, you should consider the range of values you want to represent, just select the smallest type that can overwrite the range you want to value. Choosing a larger type will waste space, make the table unnecessarily larger, and not be as effective as choosing a smaller type. For integer values, tinyint is most appropriate if the data range is small, such as the age of the person or the number of siblings. Mediumint can represent a value of millions of and can be used for more types of values, but the storage cost is higher. BigInt has the largest range of values in all integers, and the storage space required is twice times the integer int type that represents the second largest range, so it is only used when it is really needed. For floating-point values, double occupies twice times the space of float. Unless high-precision or wide-range values are particularly required, the data should generally be represented by a float type that uses only half the cost of storage.

When defining an integer column, you can specify an optional display size M. If so, M should be an integer from 1 to 255. That represents the number of characters used to display the values in the column. For example, Mediumint (4) specifies a mediumint column that has a 4-character display width. If an integer column with no explicit width is defined, it is automatically assigned a default width. The default value is the length of the maximum value for each type. If the printable representation of a particular value requires more than M characters, the full value is displayed, and the value is not truncated to fit the m character. 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.

For each floating-point type, you can specify a maximum display size of M and decimal place D. The value of M should take 1 to 255. The value of D can be 0 to 3 0, but should not be greater than m–2 (if you are familiar with ODBC terminology, you will know that M and D correspond to the "precision" and "decimal Places" of the ODBC concept). M and d are optional for both float and double, but are required for decimal. At Options M and D, if they are omitted, the default value is used.

1. Date and time data types : There are several data types in MySQL that represent dates and times. Where year represents the years, date represents the dates, time represents the times, and datetime and timestamp represent dates and practices. Specifically, the following table:

Data type	Number of bytes stored	Range of values
DATE	4	1000-01-01--9999-12-31
Time	3	-838:59:59--838:59:59
DATETIME	8	1000-01-01 00:00:00--9999-12-31 23:59:59
TIMESTAMP	4	19700101080001--20380119111407
Year	1	1901--2155

When the insertion value exceeds a valid range of values, the system will error and insert a value of 0 into the database.

MySQL is a value that displays the date type in the YYYY-MM-DD format, which can be persisted when data is inserted. In addition, MySQL also supports some of the non-strict syntax format, the delimiter "-" can be used "@", "." and other symbols to replace them. You can also use the "YY-MM-DD" format when inserting data, and the rules for converting YY to the corresponding year are similar to the years type. If we want to insert the time of the current system, we can insert current_date or now ().

The time type is expressed as "when: minutes: Seconds", although the hour range is generally 0~23, but in order to represent some special time intervals, MySQL expands the hour range of the hours and supports negative values. Assigning a value to the time type, the standard format is ' HH:MM:SS ', but not necessarily in this format. If the ' D HH:MM:SS ' format is inserted, it is similar to ' (D*24+HH): Mm:ss '. Inserting ' 2 23:50:50 ', for example, is equivalent to inserting ' 71:50:50 '. If you insert a ' hh:mm ' or ' SS ' format, the effect is to assign a value of zero to other values that are not represented by a bit. For example, insert ' 30 ', which is equivalent to inserting ' 00:00:30 ', if inserting ' 11:25 ', it is equivalent to inserting ' 11:25:00 '. In MySQL, for the ' HHMMSS ' format, the system can be automatically converted to a standard format. If we want to insert the time of the current system, we can insert current_time or now ().

The datetime type quasi-format is "Yyyy-mm-dd HH:MM:SS", and the specific assignment method is similar to the method above.

Timestamp the range of values is small, no datetime value range is large, so enter the value must be guaranteed within the range of timestamp. Its insertion is also similar to inserting other date and time data types. So how does the timestamp type insert the current time? First, you can use Current_timestamp; second, enter NULL, the system automatically enters the current TIMESTAMP; third, without any input, the system automatically enters the current TIMESTAMP. There is a special point: the value of timestamp is associated with the time zone.

There are three ways to copy the year type: the first is to insert a 4-bit string directly or 4-bit numbers, and the second is to insert a 2-bit string, in which case inserting ' 00 ' ~ ' 69 ' is equivalent to inserting 2000~2069, or inserting ' 70 ' ~ ' 99 ', which is equivalent to insert 1970~ 1999. In the second case, if it is ' 0 ', it is the same as inserting ' 00 ', which is the 2000; the third is to insert a 2-digit number, which differs from the second (inserting a two-bit string) only: If you insert a number 0, it means 0000, not 2000. So when assigning a value to year type, be sure to distinguish between 0 and ' 0 ', although the difference between the two quotes, but the actual effect is indeed a difference of 2000 years.

1. Binary data types: binary types can store any data, such as text, images, multimedia, and so on. The specific types are described below:

Data type	Description
Tityblob	Maximum length is 255 bytes
Blob	Maximum length is 64KB
Mediumblob	Maximum length is 16MB
Longblob	Maximum length is 4GB

MySQL Learning notes common data types (RPM)