MySQL Learning Series 3: Data Types
- 黄舟Original
- 2016-12-28 17:38:312674browse
BLOB data type in MYSQL
BLOB is a binary large object used to store variable amounts of data. There are 4 types of BLOB: TinyBlob, Blob, MediumBlob, and LongBlob.
The only difference between these types is the maximum size of the stored file.
MySQL’s four BLOB types type size (unit: bytes)
TinyBlob maximum 255
Blob maximum 65K
MediumBlob maximum 16M
LongBlob Maximum 4G
BLOB columns store binary strings (byte strings); TEXT columns store non-binary strings (character strings).
BLOB columns do not have a character set, and sorting and comparison are based on the numerical value of the column value bytes; TEXT columns have a character set, and values are sorted and compared based on the character set
BLOB is binary String, TEXT is a non-binary string, both can store large amounts of information. BLOB mainly stores pictures, audio information, etc., while TEXT can only store text files.
SQLSERVER
SQLSERVER does not have a BLOB data type, only a large object data type (BLOB):
text,ntext,image,nvarchar(max),varchar(max) , varbinary(max) and xml data types
Data of these data types are stored in LOB type data pages
The following is an overview of numeric column types. See Section 11.2, “Numeric Types” for details. For column storage requirements, see Section 11.5, "Column Type Storage Requirements".
M indicates the maximum display width. The maximum effective display width is 255. The display width has nothing to do with the storage size or the range of values contained in the type, as described in Section 11.2, "Numeric Types".
If ZEROFILL is specified for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.
SERIAL is an alias of BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
In the integer column definition, SERIAL DEFAULT VALUE is an alias for NOT NULL AUTO_INCREMENT UNIQUE.
Warning: It should be clear that when using the minus sign between integer values (one of which is of type UNSIGNED), the result is unsigned. See Section 12.8, “Cast Functions and Operators”.
· BIT[(M)]
Bit field type. M represents the number of bits per value, ranging from 1 to 64. If M is omitted, defaults to 1.
·TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
· BOOL, BOOLEAN
is a synonym for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.
In the future, full Boolean type processing will be introduced according to standard SQL.
· SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
Small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
· MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
Medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
· INT[(M)] [UNSIGNED] [ZEROFILL]
Normal-sized integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
· INTEGER[(M)] [UNSIGNED] [ZEROFILL]
This is a synonym for INT.
· BIGINT[(M)] [UNSIGNED] [ZEROFILL]
Big integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.
Be clear about the following contents of the BIGINT column:
o Use signed BIGINT or DOUBLE values for all algorithms, so except for bit functions, no values larger than 9223372036854775807 (63 bits) should be used Signed BIG INTEGER! If you do this, the last few digits in the result may be wrong, due to errors in rounding when converting BIGINT values to DOUBLE.
MySQL can handle BIGINT in the following situations:
§ When using integers to store large unsigned values in a BIGINT column.
§ In MIN(col_name) or MAX(col_name), where col_name refers to the BIGINT column.
§ When using operators (+, -, *, etc.) and both operands are integers.
o It is always possible to use a string to hold a strict integer value in a BIGINT column. In this case, MySQL performs a string-to-number conversion, and no double-precision representation exists in between.
o The -, + and * operators use the BIGINT algorithm when both operands are integer values. This shows that if you multiply two large integers (or from a function that returns an integer), you will get unexpected results when the result is greater than 9223372036854775807.
· FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
Small (single precision) floating point number. Allowed values are -3.402823466E+38 to -1.175494351E-38, 0 and 1.175494351E-38 to 3.402823466E+38. These are theoretical limits, based on IEEE standards. The actual range may be slightly smaller depending on the hardware or operating system.
M is the number of decimal vertical digits, and D is the number of digits after the decimal point. If M and D are omitted, the value is saved according to the limitations allowed by the hardware. Single-precision floating point numbers are accurate to approximately 7 decimal places.
If UNSIGNED is specified, negative values are not allowed.
You may encounter unexpected problems using floating point numbers because all calculations in MySQL are done with double precision. See Section A.5.7, “Resolving Problems with Unmatched Rows”.
· DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
Normal size (double precision) floating point number. Allowed values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0 and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are theoretical limits, based on IEEE standards. The actual range may be slightly smaller depending on the hardware or operating system.
M is the total number of decimal digits, and D is the number of digits after the decimal point. If M and D are omitted, the value is saved according to the limitations allowed by the hardware. Double precision floating point numbers are accurate to approximately 15 decimal places.
If UNSIGNED is specified, negative values are not allowed.
· DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)]
[UNSIGNED] [ZEROFILL]
is a synonym for DOUBLE . Except: If the SQL server mode includes the REAL_AS_FLOAT option, REAL is a synonym for FLOAT and not a synonym for DOUBLE.
· FLOAT(p) [UNSIGNED] [ZEROFILL]
Floating point number. p represents precision (expressed in bits), but MySQL only uses this value to determine whether the result column's data type is FLOAT or DOUBLE. If p is from 0 to 24, the data type becomes FLOAT without an M or D value. If p is from 25 to 53, the data type becomes DOUBLE without an M or D value. The resulting column range is the same as the single-precision FLOAT or double-precision DOUBLE data types described earlier in this section.
FLOAT(p) syntax is compatible with ODBC.
· DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
Compressed "strict" fixed-point number. M is the total number of decimal places (precision) and D is the number of digits after the decimal point (scale). The decimal point and the '-' sign (for negative numbers) are not included in M. If D is 0, the value has no decimal point or fractional part. The maximum number of digits (M) in a DECIMAL integer is 65. The maximum number of supported decimal digits (D) is 30. If D is omitted,
defaults to 0. If M is omitted, the default is 10.
If UNSIGNED is specified, negative values are not allowed.
Basic calculations of all DECIMAL columns (+, -, *, /) are done with 65-bit precision.
· DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])]
[UNSIGNED] [ZEROFILL], FIXED[(M[, D])] [UNSIGNED] [ZEROFILL]
is a synonym for DECIMAL. FIXED synonyms apply for compatibility with other servers.
11.1.2. Overview of Date and Time Types
This section provides a comprehensive discussion of temporary column types. For details, see Section 11.3, “Date and Time Types”. For column storage requirements, see Section 11.5, "Column Type Storage Requirements".
·DATE
Date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows using strings or numbers to assign values to DATE columns.
·DATETIME
A combination of date and time. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in the format 'YYYY-MM-DD HH:MM:SS', but allows using strings or numbers to assign values to DATETIME columns.
· TIMESTAMP[(M)]
Timestamp. The range is '1970-01-01 00:00:00' to 2037.
TIMESTAMP column is used to record the date and time during INSERT or UPDATE operations. If you do not assign a value, the first TIMESTAMP column in the table is automatically set to the date and time of the most recent operation. You can also set the TIMESTAMP column to the current date and time by assigning a NULL value.
TIMESTAMP value is returned and displayed as a string in the format of 'YYYY-MM-DD HH:MM:SS'. The display width is fixed at 19 characters. If you want to get a numeric value, add +0 to the TIMESTAMP column.
Note: The TIMESTAMP format used before MySQL 4.1 is not supported in MySQL 5.1; see the MySQL 4.1 Reference Manual for information about the old format.
·TIME
Time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in the format 'HH:MM:SS' but allows assigning values to TIME columns using strings or numbers.
·YEAR[(2|4)]
Year in two-digit or four-digit format. The default is four-digit format. In four-digit format, allowed values are 1901 to 2155 and 0000. In two-digit format, allowed values are 70 to 69, representing the years from 1970 to 2069. MySQL displays YEAR values in YYYY format, but allows using strings or numbers to assign values to YEAR columns.
11.1.3. Overview of String Types
This section provides a comprehensive discussion of string column types. See Section 11.4, “String Type” for details. For column storage requirements, see Section 11.5, "Column Type Storage Requirements".
In some cases, MySQL can change a string column to a type different from that given in the CREATE TABLE or ALTER TABLE statement. See Section 13.1.5.1, “Silent Column Specification Changes”.
The MySQL 5.1 string data type includes some features that were not available in versions prior to MySQL 4.1:
· Column definitions for many string data types can include the CHARACTER SET attribute that specifies the character set, Proofreading rules may also be included. (CHARSET is a synonym for CHARACTER SET). These properties apply to CHAR, VARCHAR, TEXT types, ENUM and SET. For example:
This table definition creates a column named c1, with a utf8 character set and a default collation for that character set, and a column named The column of c2 and the latin1 character set and the binary collation rules of this character set. Binary collation rules are not case-sensitive.
· MySQL 5.1 interprets length specifications in character column definitions in character units. (Some previous versions of MySQL interpreted lengths in bytes).
· For CHAR, VARCHAR and TEXT types, the BINARY attribute can assign the collation rules of the column character set to the column.
·Character columns are sorted and compared based on the character set assigned to the column. In previous versions, sorting and comparison were based on the collation rules of the server's character set. For CHAR and VARCHAR columns, you can declare the column with the BINARY attribute so that sorting and collation rules use the current character code value rather than lexical order.
For the character set support in MySQL 5.1, see Chapter 10: Character Set Support.
· [NATIONAL] CHAR(M) [BINARY| ASCII | UNICODE]
Fixed length string, padded with spaces on the right side to reach the specified length when saved. M represents the column length. The range of M is 0 to 255 characters.
Note: Trailing spaces are removed when retrieving CHAR values.
If you want to set the length of a CHAR to greater than 255, the executed CREATE TABLE or ALTER TABLE statement will fail and prompt an error:
CHAR is the abbreviation of CHARACTER. NATIONAL CHAR (or its equivalent short form NCHAR) is the standard SQL method of defining that a CHAR column should use the default character set. This is the default value in MySQL.
The BINARY attribute is the abbreviation of the binary collation rule for the specified column character set. Sorting and comparisons are based on numeric character values.
The column type CHAR BYTE is an alias of CHAR BINARY. This is to ensure compatibility.
ASCII attributes can be specified for CHAR. It allocates latin1 character set.
You can specify the UNICODE attribute for CHAR. It allocates ucs2 character set.
MySQL allows the creation of columns of type CHAR(0). This is mainly for compatibility with older versions of applications that must have a column but don't actually use the value. It's also good when you need a column that can only take two values: a CHAR(0) column not defined as NOT NULL takes up only one bit and can only take the values NULL and " (empty string).
· CHAR
This is a synonym for CHAR(1)
· [NATIONAL] VARCHAR(M) [BINARY]
M represents the maximum column. Length. The range of M is 0 to 65,535 (The maximum actual length of VARCHAR is determined by the size of the longest row and the character set used. The maximum effective length is 65,532 bytes). Standard SQL specification and does not remove trailing spaces from VARCHAR values.
VARCHAR is the abbreviation of the character VARYING. The
BINARY attribute is the abbreviation of the binary collation rule for the specified column. The comparison is based on numeric character values.
VARCHAR is saved with a one-byte or two-byte long prefix + data. If the declared length of the VARCHAR column is greater than 255, the length prefix is two bytes.
· BINARY(M)
The BINARY type is similar to the CHAR type, but holds a binary byte string instead of a non-binary string
· VARBINARY(M)
The VARBINARY type is similar to the VARCHAR type, but holds a binary byte string instead of a non-binary string.
· TINYBLOB
A BLOB with a maximum length of 255 (28–1) bytes. Column.
· TINYTEXT
TEXT column with a maximum length of 255 (28–1) characters
· BLOB[(M)]
Maximum A BLOB column of length 65,535 (216–1) bytes.
An optional length M of this type can be given. If given, MySQL creates the column as small as possible but large enough to hold M bytes long. The BLOB type of the value.
·TEXT[(M)]
A TEXT column with a maximum length of 65,535 (216–1) characters can be given. Length M. Then MySQL creates the column as the smallest TEXT type that can accommodate a value of M characters.
· MEDIUMBLOB
The maximum length is 16,777,215 (224–1) bytes. Column.
· MEDIUMTEXT
TEXT column with maximum length of 16,777,215 (224–1) characters
·LONGBLOB
Maximum length of 4,294,967,295 or 4GB. (232–1) byte BLOB column. The maximum effective (allowed) length of a LONGBLOB column depends on the maximum packet size configured in the client/server protocol and the available memory.
· LONGTEXT
TEXT column with a maximum length of 4,294,967,295 or 4GB (232–1) characters. The maximum effective (allowed) length of a LONGTEXT column depends on the maximum packet size configured in the client/server protocol and the available memory.
· ENUM(‘value1‘, ‘value2‘,…)
Enumeration type. A string that can have only one value, chosen from the value columns 'value1', 'value2', ..., NULL or the special "error value". The ENUM column can have up to 65,535 distinct values. ENUM values are used internally Integer representation.
· SET('value1','value2',…)
A string object can have zero or more values, and each value must come from a column value. 'value1', 'value2', ... SET columns can have up to 64 members. SET values are represented internally by integers.
##11.2. Numeric types
MySQL supports. All standard SQL numeric data types. These types include strict numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), and approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION). The keyword INT is a synonym for INTEGER, and the keyword DEC is. Synonyms for DECIMAL.
As an extension of the SQL standard, MySQL also supports integer types TINYINT, MEDIUMINT and BIGINT. The following table shows the storage and range required for each integer type
Type Bytes Minimum Value Maximum
(Signed/Unsigned) ( Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
##0 65535 MEDIUMINT 3 -8388608 8388607 0 16777215 INT 4 -2147483648 2147483647 0 429496 7295 BIGINT 8 -9223372036854775808 9223372036854775807 0 18446744073709551615MySQL also supports the option of specifying the display width of an integer value in parentheses following the type keyword (for example, INT(4)). The optional display width specification is used to fill the width from the left when the display width is smaller than the specified column width.
Display width does not limit the range of values that can be saved within the column, nor does it limit the display of values that exceed the specified width of the column.
When used in combination with the optional extended attribute ZEROFILL, the default supplementary spaces are replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, the value 4 is retrieved as 00004. Please note that if you store a value in an integer column that exceeds the display width, MySQL will run into problems when generating temporary tables for complex joins, because in these cases MySQL believes that the data fits the original column width.
All integer types can have an optional (non-standard) attribute UNSIGNED. Unsigned values can be used when you want to allow only non-negative numbers in a column and the column requires a larger upper numeric range.
Floating-point and fixed-point types can also be UNSIGNED. For the same number type, this property prevents negative values from being saved in the column. However, unlike integer types, the upper range of column values remains unchanged.
If ZEROFILL is specified for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.
For floating-point column types, single-precision values use 4 bytes and double-precision values use 8 bytes in MySQL.
The FLOAT type is used to represent approximate numeric data types. The SQL standard allows the option of specifying precision in bits (but not exponential ranges) within parentheses following the keyword FLOAT. MySQL also supports optional precision specifications that are used only to determine storage size. Precisions from 0 to 23 correspond to the 4-byte single precision of the FLOAT column. Precisions from 24 to 53 correspond to the 8-byte double precision of the DOUBLE column.
MySQL allows the use of non-standard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE
PRECISION(M,D). Here, "(M,D)" means that the value displays a total of M-digit integers, where D-digits are located after the decimal point. For example, a column defined as FLOAT(7,4) could be displayed as -999.9999. MySQL rounds the value when saving it, so if you insert 999.00009 into the FLOAT(7,4) column, the approximate result is 999.0001.
MySQL treats DOUBLE as a synonym for DOUBLE PRECISION (non-standard extension). MySQL also treats REAL as a synonym for DOUBLE PRECISION (non-standard extension) unless the SQL server mode includes the REAL_AS_FLOAT option.
To ensure the greatest possible portability, code that requires the storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION, without specifying precision or number of digits.
DECIMAL and NUMERIC types are considered the same type in MySQL. They are used to hold values that must be of exact precision, such as currency data. When declaring a column of this type, you can (and usually do) specify precision and scale; for example:
In this example, 5 is the precision and 2 is the scale. Precision indicates how many digits can be saved in the value, and scale indicates how many digits can be saved after the decimal point.
Save DECIMAL and NUMERIC values in binary format in MySQL 5.1.
Standard SQL requires that the salary column can store any value with 5 integer digits and two decimal places. Therefore, in this case the range of values that can be saved in the salary column is from -999.99 to 999.99.
In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0), and the value of M can be determined through calculation. Variable forms of DECIMAL and NUMERIC data types are supported in MySQL
5.1. The default value of M is 10.
The maximum number of digits for a DECIMAL or NUMERIC is 65, but the actual range of a specific DECIMAL or NUMERIC column is subject to the precision or scale of the specific column. If such a column is assigned a value with more digits after the decimal point than allowed by the specified scale, the value is converted to that scale. (The specific operation depends on the operating system, but generally the results are truncated to the allowed number of digits).
The BIT data type can be used to save bit field values. The BIT(M) type allows storage of M-bit values. M ranges from 1 to 64.
To specify a bit value, you can use the b'value' character. value is a binary value written with 0 and 1. For example, b’111′ and b’100000000′ represent 7 and 128 respectively. See Section 9.1.5, “Bitfield Values”.
If the length of the value assigned to the BIT(M) column is less than M bits, pad the left side of the value with 0s. For example, assigning a value b'101' to the BIT(6) column has the same effect as assigning b'000101'.
When you want to save a value in a numeric column that exceeds the allowed range of the column, MySQL's operation depends on the SQL mode in effect at the time. If mode is not set, MySQL clips the value to the corresponding endpoint of the range and saves the clipped value. However, if the mode is set to traditional ("strict mode"), out-of-range values will be rejected with an error, and inserts will fail according to SQL standards. See Section 5.3.2, “SQL Server Mode”.
If the INT column is UNSIGNED, the size of the column range will be the same, but its endpoints will change to 0 and 4294967295. If you try to save -9999999999 and 9999999999, the values saved into the column in non-strict mode are 0 and 4294967296.
If the value assigned in a floating-point or fixed-point column exceeds the range specified by the specified (or default) precision and scale, MySQL saves the value representing the corresponding endpoint of the range in non-strict mode.
When MySQL is not working in strict mode, conversions due to clipping will be reported as warnings for ALTER TABLE, LOAD DATA INFILE, UPDATE, and multi-row INSERT statements. When MySQL is working in strict mode, these statements will fail and some or all values will not be inserted or changed, depending on whether the table is transactional and other factors. See Section 5.3.2, "SQL Server Mode" for details.
11.3. Date and time types
11.3.1. DATETIME, DATE and TIMESTAMP types 11.3.2. TIME type 11.3.3. YEAR type 11.3.4. Y2K matters and date types
DATE and time types representing time values are DATETIME , DATE, TIMESTAMP, TIME and YEAR. Each time type has a range of valid values and a "zero" value, which is used when specifying an illegal value that MySQL cannot represent. The TIMESTAMP type has proprietary automatic update features, which will be described later.
If you try to insert an illegal date, MySQL will give a warning or error. You can use the ALLOW_INVALID_DATES SQL mode to let MySQL accept certain dates, such as '1999-11-31'. Useful when you want to save a "possibly wrong" value that the user has specified in the database (for example, in a web form) for future processing. In this mode, MySQL only verifies that the month range is from 0 to 12 and the day range is from 0 to 31. These ranges can include zero because MySQL allows saving day/month and dates where day is zero in a DATE or DATETIME column. This is useful when an application needs to save a birthday for which you don't know the exact date. In this case, just save the date as '1999-00-00′ or '1999-01-00′. Functions that require a complete date such as DATE_SUB() or DATE_ADD will not give correct results if you save such a date. (If you don't want zeros to appear in the date, you can use the NO_ZERO_IN_DATE SQL mode).
MySQL also allows '0000-00-00' to be saved as a "pseudo date" (if NO_ZERO_DATE SQL mode is not used). This is more convenient in some cases than using NULL values (and the data and indexes take up less space).
Set the sql_mode system variable to the corresponding mode value to know more clearly what kind of dates you want MySQL to support. See Section 5.3.2, “SQL Server Mode”.
The following points should be kept in mind when working with date and time types:
·MySQL retrieves values of a given date or time type in standard output format, but it does its best to interpret each value you specify. An input value format (for example, when you specify a value that is assigned to or compared to a date or time type). Only the formats described in the following sections are supported. You are expected to provide valid values. Unexpected results may occur if you use values in other formats.
· Dates containing two-digit year values are confusing because the century is not known. MySQL uses the following rules to interpret two-digit year values:
o Year values in the range 70-99 are converted to 1970-1999.
o Yearly values in the range 00-69 are converted to 2000-2069.
· Although MySQL attempts to interpret values in several formats, dates are always in year-month-day order (for example, '98-09-04′), rather than month-day-year as is commonly used elsewhere. Or day-month-year order (for example, '09-04-98', '04-09-98').
· MySQL automatically converts a date or time type value to a number if the value is used in a numeric context, and vice versa.
· When MySQL encounters a date or time type value that is out of range or illegal for that type (as described at the beginning of this section), it converts the value to the "zero" value of that class . One exception is that out-of-range TIME values are clipped to the corresponding endpoint of the TIME range.
The following table shows the format of various "zero" values. Please note that using these values will generate warnings if NO_ZERO_DATE SQL mode is enabled.
Column type "zero" value
DATETIME '0000-00-00 00:00:00′
DATE '0000-00-00′
TIMESTAMP 00000000000000
TIME '00:00:00′
YEAR 0000
· The "zero" value is a special value, but you can use the table display The values are saved explicitly or refer to them. You can also use the value '0' or 0 to save or reference, which is easier to write.
· "Zero" date or time values used in MyODBC are automatically converted to NULL in MyODBC 2.50.12 and above because ODBC cannot handle such values.
11.3.1. DATETIME, DATE, and TIMESTAMP types
11.3.1.1.
TIMESTAMP attribute since MySQL 4.1
DATETIME, DATE, and TIMESTAMP types are related. This section describes their characteristics, their similarities and differences.
Use the DATETIME type when you need a value that contains both date and time information. MySQL retrieves and displays DATETIME values in the 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. ("Supported" means that while the previous value may work, there is no guarantee).
The DATE type should be used when you only need the date value without the time part. MySQL uses the 'YYYY-MM-DD' format to retrieve and display DATE values. The supported range is '1000-01-01' to '9999-12-31'.
The properties of the TIMESTAMP column type are not fixed and depend on the MySQL version and the SQL mode the server is running in. These properties are described later in this section.
DATETIME, DATE, and TIMESTAMP values can be specified using any common format:
· 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS 'Format string. "Relaxed" syntax is allowed: any punctuation character can be used as a separator between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45' and '98@12@31 11^30^45 ′ are equivalent.
· A string in the format of 'YYYY-MM-DD' or 'YY-MM-DD'. "Relaxed" syntax is also allowed here. For example, '98-12-31', '98.12.31', '98/12/31' and '98@12@31' are equivalent.
· A string without separators in the format of 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS', assuming that the string is meaningful for date types. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a meaningless minutes part) and will become '0000- 00-00 00:00:00′.
· A string without separators in the format of 'YYYYMMDD' or 'YYMMDD', assuming that the string is meaningful for date types. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has a meaningless month and day part) and will become '0000-00-00' '.
· A number in YYYYMMDDHHMMSS or YYMMDDHHMMSS format, assuming the number makes sense for the date type. For example, 19830905132800 and 830905132800 are interpreted as ’1983-09-05 13:28:00′.
· A number in YYYYMMDD or YYMMDD format, assuming the number makes sense for the date type. For example, 19830905 and 830905 are interpreted as '1983-09-05'.
· The result returned by the function has a value suitable for DATETIME, DATE or TIMESTAMP context, such as NOW() or CURRENT_DATE.
Invalid DATETIME, DATE or TIMESTAMP values are converted to a "zero" value of the corresponding type ('0000-00-00 00:00:00', '0000-00-00' or 00000000000000).
For string values that include date part separators, if the day and month values are less than 10, there is no need to specify two digits. '1979-6-9' is the same as '1979-06-09'. Likewise, for string values that include time part separators, if the hours, minutes, and seconds values are less than 10, there is no need to specify two digits. '1979-10-30 1:2:3' is the same as '1979-10-30 01:02:03'.
The numeric value should be 6, 8, 12 or 14 digits long. If a number is 8 or 14 digits long, it is assumed to be in YYYYMMDD or YYYYMMDDHHMMSS format, with the first 4 digits representing the year. If the number is 6 or 12 digits long, it is assumed to be in YYMMDD or YYMMDDHHMMSS format, with the first 2 digits representing the year. Other numbers are interpreted as if zero-padded to the nearest length.
Values specified as unqualified strings are interpreted using the given length. If the string is 8 or 14 characters long, the first 4 digits represent the year. Otherwise, the first 2 digits represent the year. Interpret each component occurring within the string from left to right to discover the year, month, day, hour, minute, and second values. This means that strings shorter than 6 characters should not be used. For example, if you specify '9903', thinking it means March 1999, MySQL will insert a "zero" date value into your table. This is because the year and month values are 99 and 03, but the day part is completely missing, so the value is not a legal date. However, you can explicitly specify a zero value to represent the missing month or day part. For example, you can use '990300' to insert the value '1999-03-00'.
To a certain extent, it is possible to assign a value of a date type to a different date type. However, the value may change or lose some information:
· If you assign a DATE value to a DATETIME or TIMESTAMP object, the time portion of the resulting value is set to '00:00:00' because of the DATE value No time information included.
· If you assign a DATETIME or TIMESTAMP value to a DATE object, the time portion of the resulting value is removed because the DATE value does not contain time information.
· Remember that although DATETIME, DATE, and TIMESTAMP values can be specified using the same format, the ranges for the different types of values are different. For example, TIMESTAMP value cannot be earlier than 1970 or later than 2037. This indicates that a date, such as '1968-01-01', while valid for DATETIME or DATE values, is not valid for TIMESTAMP values and will be converted to 0 if assigned to such an object.
Please be aware of certain pitfalls when specifying date values:
· The non-strict format allowed for values specified as strings may be deceptive. For example, the value '10:11:12' may look like a time value due to the ':' separator, but if used in a date context value is interpreted as the year '2010-11-12'. The value '10:45:15' is converted to '0000-00-00' because '45' is not a legal month.
· In non-strict mode, the MySQL server only performs basic checks on the validity of dates: the ranges of year, month and day are 1000 to 9999, 00 to 12 and 00 to 31 respectively. Any date containing parts outside these ranges is converted to '0000-00-00′. Note that you are still allowed to save illegal dates, such as '2002-04-31'. To ensure that dates are valid when not using strict mode, you should check your application.
In strict mode, illegal dates are not accepted and are not converted.
For details, see Section 5.3.2, "SQL Server Mode".
· Dates containing two-digit year values are confusing because the century is not known. MySQL uses the following rules to interpret two-digit year values:
o Year values in the range 00-69 are converted to 2000-2069.
o Yearly values in the range 70-99 are converted to 1970-1999.
11.3.1.1. TIMESTAMP attributes since MySQL 4.1
Note: In older versions of MySQL (prior to 4.1), the attributes of the TIMESTAMP column type were in many ways Very different from what is described in this section. If you need to convert old TIMESTAMP data to work in MySQL 5.1, see the MySQL 4.1 Reference Manual for details.
The display format of the TIMESTAMP column is the same as the DATETIME column. In other words, the display width is fixed at 19 characters, and the format is YYYY-MM-DD HH:MM:SS.
MySQL server can also run in MAXDB mode. When the server is running in this mode, TIMESTAMP is equal to DATETIME. That is, if the server is running in MAXDB mode when the table is created, the TIMESTAMP column is created as a DATETIME column. The result is that the column uses the DATETIME display format, has the same value range, and is not automatically initialized or updated with the current date and time.
To enable MAXDB mode, use the –sql-mode=MAXDB server option when starting the server or set the SQL server mode to MAXDB at runtime by setting the global sql_mode variable:
The client can have the server run in MAXDB mode for its connection as follows:
MySQL does not accept data in Japan or the month column contains a zero or a timestamp value that contains an illegal date value. The only exception to this rule is the special value '0000-00-00 00:00:00′.
You can very flexibly determine when to initialize and update TIMESTAMP and which columns to initialize and update:
· You can specify the current timestamp as the default value and automatically updated value . But you can only choose one, or neither. (It is not possible for one column to select one behavior and another column to select another behavior).
· You can specify which TIMESTAMP column is automatically initialized or updated to the current date and time. The 1st TIMESTAMP column is no longer needed.
Please note that the information discussed below only applies to TIMESTAMP columns in tables that were created without MAXDB mode enabled. (As mentioned above, MAXDB mode causes columns to be created as DATETIME columns). The rules that control the initialization and updating of TIMESTAMP columns are as follows:
· If the first TIMESTAMP column in a table is specified as a DEFAULT value, it cannot be ignored. The default value can be CURRENT_TIMESTAMP or a constant date and time value.
· DEFAULT NULL is the same as DEFAULT CURRENT_TIMESTAMP of the first TIMESTAMP column. For other TIMESTAMP columns, DEFAULT NULL is treated as DEFAULT 0.
· Any TIMESTAMP column in the table can be set to automatically initialize to the current timestamp and/or update.
· In the CREATE TABLE statement, you can declare the first TIMESTAMP column in any of the following ways:
o Use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses to use the default value. The current timestamp and updated automatically.
o Do not use DEFAULT or ON UPDATE clause, same as DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP.
o Use the DEFAULT CURRENT_TIMESTAMP clause instead of the ON UPDATE clause. The column uses the current timestamp as the default value but does not update automatically.
o Without the DEFAULT clause but with the ON UPDATE CURRENT_TIMESTAMP clause, the column has a default value of 0 and is automatically updated.
o Use the constant DEFAULT value, and the default value given is listed. If the column has an ON UPDATE CURRENT_TIMESTAMP clause, it updates automatically, otherwise it does not.
In other words, you can use the current timestamp for the initial value and the automatically updated value, or one of them, or neither. (For example, you can specify ON UPDATE to enable automatic updates without having the columns automatically initialized).
· You can use CURRENT_TIMESTAMP, CURRENT_TIMESTAMP() or NOW() in the DEFAULT and ON UPDATE clauses. They all have the same effect.
The order of the two properties is not important. If both DEFAULT and ON UPDATE are specified for a TIMESTAMP column, either can precede the other.
Example, the following statements are equivalent:
· To specify automatic default or update for a TIMESTAMP column other than column 1, you must disable automatic initialization and update by explicitly assigning the first TIMESTAMP column a constant DEFAULT value. (For example, DEFAULT 0 or DEFAULT’2003-01-01 00:00:00′). Then, for other TIMESTAMP columns, the rules are the same as for the first TIMESTAMP column, with the exception that the DEFAULT and ON UPDATE clauses cannot be ignored. If you do this, no initialization or update will occur automatically.
For example: The following statements are equivalent:
You can set the current time zone for each connection, See Section 5.10.8, "MySQL Server Time Zone Support" for related descriptions. TIMESTAMP values are saved in UTC format, converted to the current time zone when stored, and converted back to the current time zone when retrieved. As long as the time zone setting value is a constant, the value at the time of saving can be obtained. If you save a TIMESTAMP value, you should change the time zone and then retrieve the value, it will be different from the value you saved. This is because the same time zone is not used in the conversion in both directions. The current time zone can be used as the value of the time_zone system variable.
You can include the NULL attribute in the definition of a TIMESTAMP column to allow the column to contain NULL values. For example:
#If the NULL attribute is not specified, setting the column to NULL will set it to the current timestamp. Please note that a TIMESTAMP column that allows NULL values will not assume the current timestamp unless either its default value is defined as CURRENT_TIMESTAMP, or NOW() or CURRENT_TIMESTAMP is inserted into the column. In other words, a TIMESTAMP column defined as NULL will automatically update only if it is created with the following definition:
Otherwise - that is, if NULL is used instead DEFAULT TIMESTAMP to define the TIMESTAMP column as follows...
... then you must explicitly insert a value corresponding to the current date and time. For example:
##11.3.2. TIME type
You can specify the TIME value in various formats:
· A string in the format of 'D HH:MM:SS.fraction'. You can also use any of the following "non-strict" syntaxes: 'HH:MM:SS.fraction', 'HH:MM:SS', 'HH:MM', 'D HH:MM:SS', 'D HH: MM', 'D HH' or 'SS'. Here D represents the day, which can take a value between 0 and 34. Please note that MySQL does not save scores yet.
· A string in the 'HHMMSS' format without separators, assuming a meaningful time. For example, '101112' is understood as '10:11:12', but '109712' is illegal (it has a meaningless minutes part) and will become '00:00:00'.
· The value in HHMMSS format is assumed to be a meaningful time. For example, 101112 is understood as '10:11:12'. The following formats are also understood: SS, MMSS, HHMMSS, HHMMSS.fraction. Please note that MySQL does not save scores yet.
· The result returned by the function has a value suitable for the TIME context, such as CURRENT_TIME.
For TIME values specified as strings that include the time part separator, if the hour, minute, or second value is less than 10, you do not need to specify two digits. '8:3:2' is the same as '08:03:02'.
You should pay attention when assigning abbreviated values to the TIME column. Without the colon, MySQL interprets the value assuming that the rightmost two digits represent seconds. (MySQL interprets TIME values as time in the past rather than time today). For example, you might think '1112' and 1112 mean '11:12:00' (12 minutes past 11 o'clock), but MySQL interprets them as '00:11:12' (11 minutes, 12 seconds). Likewise, '12' and 12 are interpreted as '00:00:12'. In contrast, a colon in a TIME value is always considered the time of day. That is, '11:12' means '11:12:00', not '00:11:12'.
Values that are outside the TIME range but are legal are trimmed to the closest endpoint of the range. For example, '-850:00:00' and '850:00:00' are converted to '-838:59:59' and '838:59:59'.
Invalid TIME values are converted to '00:00:00'. Please note that since '00:00:00' itself is a legal TIME value, only a '00:00:00' value saved in the table cannot tell whether the original value is '00:00:00' or illegal. value.
11.3.3. YEAR type
The YEAR type is a single-byte type used to represent the year.
MySQL retrieves and displays YEAR values in YYYY format. The range is 1901 to 2155.
You can specify YEAR values in various formats:
· Four-digit string, ranging from '1901' to '2155'.
· Four digits, ranging from 1901 to 2155.
· Two-digit string, ranging from '00' to '99'. Values in the range '00' to '69' and '70' to '99' are converted to YEAR values in the range 2000 to 2069 and 1970 to 1999.
· Two-digit integer in the range 1 to 99. Values in the range 1 to 69 and 70 to 99 are converted to YEAR values in the range 2001 to 2069 and 1970 to 1999. Note that two-digit integer ranges are slightly different from two-digit string ranges in that you cannot directly specify zero as a number and have it interpreted as 2000. You must specify it as a string '0' or '00' or it will be interpreted as 0000.
· The result returned by the function has a value suitable for the YEAR context, such as NOW().
Illegal YEAR values are converted to 0000.
11.3.4. Y2K Issues and Date Types
MySQL is inherently safe for Year 2000 (Y2K) (see Section 1.4.5, “Y2K Compatibility "), but the value entered into MySQL may not be safe. Any input containing a two-digit year value will be ambiguous because the century is not known. These values must be interpreted as four digits because MySQL uses four digits internally to store the year.
For DATETIME, DATE, TIMESTAMP, and YEAR types, MySQL uses the following rules to interpret dates with ambiguous year values:
· Year values in the range of 00-69 are converted to 2000-2069.
· Annual values in the range 70-99 are converted to 1970-1999.
Remember these rules are just reasonable guesses at what the data values represent. If the heuristics used by MySQL do not produce the correct value, you should provide the exact input containing the four-digit year value.
ORDER BY can correctly sort TIMESTAMP or YEAR values with two digits of year.
Some functions such as MIN() and MAX() convert TIMESTAMP or YEAR into a number. This means that these functions do not work correctly with values that have two-digit year values. The fix in this case is to convert TIMESTAMP or YEAR to four-digit year format or use MIN(DATE_ADD(TIMESTAMP,INTERVAL 0 DAYS)).
11.4. String type
11.4.1. CHAR and VARCHAR types 11.4.2.
BINARY and VARBINARY types 11.4.3. BLOB and TEXT types
11.4.4. ENUM type 11.4.5. SET type
String types refer to CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM and SET. This section describes how these types work and how to use them in queries.
11.4.1. CHAR and VARCHAR types
CHAR and VARCHAR types are similar, but they are saved and retrieved differently. They also differ in terms of their maximum length and whether trailing spaces are preserved. No case conversion is performed during storage or retrieval.
The declared length of CHAR and VARCHAR types indicates the maximum number of characters you want to save. For example, CHAR(30) can occupy 30 characters.
The length of the CHAR column is fixed to the length declared when creating the table. The length can be any value from 0 to 255. When saving CHAR values, pad them to the right with spaces to the specified length. When a CHAR value is retrieved, trailing spaces are removed. No case conversion is performed during storage or retrieval.
The values in the VARCHAR column are variable-length strings. The length can be specified as a value between 0 and 65,535. (The maximum effective length of VARCHAR is determined by the maximum row size and the character set used. The overall maximum length is 65,532 bytes).
Compared with CHAR, when the VARCHAR value is saved, only the required number of characters is saved, plus one byte to record the length (if the length of the column declaration exceeds 255, two bytes are used).
VARCHAR values are not filled when saved. Trailing spaces are retained when the value is saved and retrieved, conforming to standard SQL.
If the value assigned to a CHAR or VARCHAR column exceeds the maximum length of the column, the value is clipped to fit. If the truncated character is not a space, a warning is generated. If non-space characters are trimmed, this causes an error (instead of a warning) and disables the insertion of values by using strict SQL mode. See Section 5.3.2, “SQL Server Mode”.
The following table shows the results of saving various string values into CHAR(4) and VARCHAR(4) columns, illustrating the difference between CHAR and VARCHAR:
Value CHAR(4) Storage requirements VARCHAR(4) Storage requirements
” ' ' 4 bytes ” 1 byte
'ab' 'ab ' 4 bytes ' ab ' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes Bytes
Please note that the value in the last row in the above table only applies when strict mode is not used; if MySQL is running in strict mode, values exceeding the column length will not be saved, and an error will occur.
The values retrieved from CHAR(4) and VARCHAR(4) columns are not always the same because trailing spaces are removed from the CHAR column when retrieving. The following example illustrates the difference:
Sorts and compares values in CHAR and VARCHAR columns according to the character set collation rules assigned to the column.
Please note that all MySQL collation rules belong to the PADSPACE class. This means that all CHAR and VARCHAR values in MySQL do not need to consider any trailing spaces when comparing them. For example:
Please note that this is true for all MySQL versions, and it is not affected by the SQL server mode.
For cases where the trailing padding characters are clipped or ignored during comparison, if the column index requires a unique value, inserting a value into the column that differs only in the number of padding characters will result in a duplicate key value. mistake.
CHAR BYTE is an alias of CHAR BINARY. This is to ensure compatibility.
The ASCII attribute assigns the latin1 character set to the CHAR column. The UNICODE attribute assigns the ucs2 character set.
11.4.2. BINARY and VARBINARY types
The BINARY and VARBINARY classes are similar to CHAR and VARCHAR, except that they contain binary strings instead of non-binary strings . That is, they contain byte strings rather than character strings. This means that they do not have a character set, and sorting and comparison are based on the numeric value of the column value bytes.
The maximum allowed lengths of BINARY and VARBINARY are the same, just like CHAR and VARCHAR. The difference is that the length of BINARY and VARBINARY is the length of bytes rather than the length of characters.
The BINARY and VARBINARY data types are different from the CHAR BINARY and VARCHAR BINARY data types. For the latter type, the BINARY property does not treat the column as a binary string column. Instead, it causes the binary collation rules of the column's character set to be used, and the column itself to contain non-binary character strings rather than binary byte strings. For example, CHAR(5) BINARY is treated as CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin, assuming that the default character set is latin1. This is different from BINARY(5), which holds a 5-byte binary string with no character set or collation rules.
When saving BINARY values, pad them on the right with values to reach the specified length. The padding value is 0×00 (zero bytes). Adds 0×00 on on the right when inserting a value, and does not delete trailing bytes when selecting. All bytes are important when comparing, including ORDER BY and DISTINCT operations. When comparing, 0×00 bytes and spaces are different, 0×00
For example: For a BINARY(3) column, 'a' becomes 'a ' when inserted. 'a' becomes 'a' when inserted. Neither inserted value changes when selected.
For VARBINARY, characters are not padded when inserted and bytes are not trimmed when selected. All bytes are important when comparing, including ORDER BY and DISTINCT operations. When comparing, 0×00 bytes and spaces are different, 0×00
For cases where the trailing padding characters are clipped or ignored during comparison, if the column index requires a unique value, inserting a value into the column that differs only in the number of padding characters will result in a duplicate key value. mistake.
If you plan to use these data types to save binary data and need to retrieve the exact same value as the saved value, you should consider the padding and clipping characteristics described previously. The following example illustrates how a BINARY value filled with 0×00 affects column value comparison:
if The value retrieved must be the same as the value specified for storage without padding, preferably using the BLOB data type.
When creating a table, MySQL can silently change the type of a BINARY or VARBINARY column. See Section 13.1.5.1, “Silent Column Specification Changes”.
11.4.3. BLOB and TEXT types
BLOB is a binary large object that can hold a variable amount of data. There are 4 BLOB types: TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB. They only differ in the maximum length they can hold a value.
There are 4 TEXT types: TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. These correspond to 4 BLOB types, with the same maximum length and storage requirements.
See Section 11.5, "Column Type Storage Requirements".
BLOB columns are treated as binary strings (byte strings). TEXT columns are treated as non-binary strings (character strings). BLOB columns do not have a character set, and sorting and comparison are based on the numeric value of the column value bytes. TEXT columns have a character set, and values are sorted and compared according to the character set's collation rules.
There is no case conversion during storage or retrieval of TEXT or BLOB columns.
When not running in strict mode, if you assign a value to a BLOB or TEXT column that exceeds the maximum length of the column type, the value is truncated to ensure it fits. If the truncated character is not a space, a warning will be generated. Using strict SQL mode, an error will be generated and the value will be rejected instead of intercepted with a warning. See Section 5.3.2, “SQL Server Mode”.
In most respects, a BLOB column can be treated as a VARBINARY column that can be large enough. Likewise, TEXT columns can be treated as VARCHAR columns. BLOB and TEXT differ from VARBINARY and VARCHAR in the following ways:
· Trailing spaces are not removed when saving or retrieving values from BLOB and TEXT columns. (This is the same as VARBINARY and VARCHAR columns).
Please note that TEXT will be expanded with spaces to fit the comparison object, just like CHAR and VARCHAR.
· For indexes on BLOB and TEXT columns, the length of the index prefix must be specified. For CHAR and VARCHAR, the prefix length is optional. See Section 7.4.3, “Column Indexes”.
· BLOB and TEXT columns cannot have default values.
LONG and LONG VARCHAR correspond to the MEDIUMTEXT data type. This is to ensure compatibility. If the TEXT column type uses the BINARY attribute, the column will be assigned the binary collation of the column character set.
MySQL Connector/ODBC defines BLOB values as LONGVARBINARY and TEXT values as LONGVARCHAR.
Since BLOB and TEXT values may be very long, you may encounter some constraints when using them:
· Only the first max_sort_length bytes of the column are used when sorting. The default value of max_sort_length is 1024; this value can be changed using the –max_sort_length option when starting the mysqld server. See Section 5.3.3, "Server System Variables".
Increasing the value of max_sort_length at runtime can make more bytes meaningful when sorting or combining. Any client can change the value of its session max_sort_length variable:
When you want to make sense of bytes exceeding max_sort_length, right Another way to use GROUP BY or ORDER BY for BLOB or TEXT columns containing long values is to convert the column values into fixed-length objects. The standard approach is to use the SUBSTRING function. For example, the following statement sorts 2000 bytes of the comment column:
The maximum size of a BLOB or TEXT object is determined by its type, but is not specified in the client The actual maximum value that can be transferred between the client and the server is determined by the amount of available memory and the size of the communication buffer. You can change the size of the message buffer by changing the value of the max_allowed_packet variable, but you must modify both the server and client programs. For example, you can use mysql and mysqldump to change the client's max_allowed_packet value. See Section 7.5.2, "Adjusting Server Parameters", Section 8.3, "mysql: MySQL Command Line Tool", and Section 8.8, "mysqldump: Database Backup Program".
Each BLOB or TEXT value is represented by an internally allocated object. This is in contrast to other column types, which allocate a storage engine to each column when the table is opened.
11.4.4. ENUM type
ENUM is a string object whose values come from a column of values that are explicitly enumerated in the column specification when the table is created.
In some cases, ENUM values can also be empty strings (") or NULL:
· If you insert an illegal value into ENUM (that is, the allowed values column string), an empty string is inserted as a special error value. This string is different from the "normal" empty string, which has the numeric value 0.
##· If an ENUM column is declared to allow NULL, the NULL value is a valid value for the column, and the default value is NULL. If the ENUM column is declared NOT NULL, its default value is the first element of the allowed value column #.
##Each enumeration value has an index:
· Values in the allowed values column specified from the column are numbered starting from 1
· Empty string error value. The index value is 0. This means that you can use the following SELECT statement to find rows with illegal ENUM values assigned:
Value Index
Enumerations can have up to 65,535 elements.
When creating a table, trailing spaces in ENUM member values will be automatically removed.
When retrieving, the values stored in the ENUM column are displayed using the case used in the column definition. Please note that ENUM columns can be assigned character sets and collation rules. For binary or case-sensitive collation rules, case should be considered when assigning values to columns.
If an ENUM value is retrieved in a numeric context, the index of the column value will be returned. For example, you can search for numeric values from an ENUM column like this:
If you save a number to an ENUM column, the number is treated as an index, and the saved value is The enumeration member corresponding to this index. (However, this does not work with LOAD DATA, which treats all input as strings). It is not recommended to define an ENUM column using numeric-like enumeration values, as this can easily cause confusion. For example, the following column contains enumeration members with string values '0', '1', and '2', but numeric index values 1, 2, and 3:
Sort ENUM values according to the order in which the enumeration members are listed in the column definition. (In other words, ENUM values are sorted according to index number). For example, for ENUM('a', 'b'), 'a' comes before 'b', but for ENUM('b', 'a'), 'b' comes before 'a'. Empty strings are sorted before non-empty strings, and NULL values are sorted before all other enumeration values. To prevent unexpected results, specify ENUM columns in alphabetical order. You can also use GROUP BY CAST(col AS CHAR) or GROUP BY CONCAT(col) to ensure that columns are sorted lexically rather than numerically.
If you want to determine all possible values for an ENUM column, use SHOW COLUMNS FROM tbl_name LIKE enum_col, and parse the ENUM definition of column 2 in the output.
11.4.5. SET type
SET is a string object that can have zero or more values. Its value comes from one of the allowed columns specified when the table was created. value. When specifying a SET column value that includes multiple SET members, use commas (‘,’) to separate each member. This way the SET member value itself cannot contain commas.
For example, a column specified as SET('one', 'two') NOT NULL can have any of the following values:
SET There can be up to 64 different members.
When creating a table, trailing spaces in SET member values will be automatically removed.
When retrieving, the values stored in the SET column are displayed using the case used in the column definition. Please note that SET columns can be assigned character sets and collation rules. For binary or case-sensitive collation rules, case should be considered when assigning values to columns.
MySQL uses numbers to save SET values, and the low-order bits of the saved values correspond to the first SET member. If a SET value is retrieved in a numeric context, the bit settings of the retrieved value correspond to the SET members that make up the column value. For example, you can retrieve a numeric value from a SET column like this:
If you save a number to a SET column, the number of bits in the binary representation is determined SET member in column value. For columns specified as SET('a','b','c','d'), members have the following decimal and binary values:
SET Members Decimal Value Binary Value
'a' 1 0001
'b' 2 0010
'c' 4 0100
'd' 8 1000
If you The column is assigned a value of 9, which in binary form is 1001, so the 1st and 4th SET value members 'a' and 'd' are selected and the resulting values are 'a,d'.
For values that contain multiple SET elements, the order in which the elements are listed does not matter when inserting the value. It doesn't matter how many times a given element is listed in the value. When the value is later retrieved, each element in the value appears once, listing the elements in the order specified when the table was created. For example, assume a column is specified as SET('a','b','c','d'):
Insert the value 'a, d', 'd,a', 'a,d,d', 'a,d,a' and 'd,a,d':
When retrieving all these values appear as 'a,d':
If a SET column is set to an unsupported value, the value is ignored and warns:
Normally, you can use the FIND_IN_SET() function or the LIKE operator to search for the SET value:
The following statements are also legal:
If you want to determine all possible values for a SET column, use SHOW COLUMNS FROM tbl_name LIKE set_col and parse the SET definition for column 2 in the output.
11.5. Column type storage requirements
The storage requirements for each column type supported by MySQL are listed according to category.
The maximum size of a row in a MyISAM table is 65,534 bytes. Each BLOB and TEXT column accounts for only 5 to 9 bytes of it.
If the MyISAM table includes variable-length column types, the record format is also variable-length. When creating a table, MySQL can change a column from a variable-length type to a fixed-length type or vice versa, under certain conditions. See Section 13.1.5.1, "Silent Column Specification Changes" for details.
Numeric type storage requirements
Column type Storage requirements
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT, INTEGER 4 bytes
BIGINT 8 bytes
FLOAT(p) If 0 <= p <= 24 is 4 bytes, if 25 <= p <= 53 is 8 bytes
FLOAT 4 bytes
DOUBLE [PRECISION], item REAL 8 bytes
DECIMAL(M,D), NUMERIC(M,D) Variable length; see discussion below
BIT(M) About (M+7)/8 bytes
The storage requirements of DECIMAL (and NUMERIC) are version-specific:
Use binary format to compress 9 decimal (based on 10) numbers into 4 bytes to represent the DECIMAL column value. The storage of the integer and fractional parts of each value is determined separately. Each multiple of 9 digits requires 4 bytes, and the "remaining" bits require a portion of the 4 bytes. The following table gives the storage requirements for excess bits:
Remaining Bytes
Number of Bits
0 0
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4
9 4
Storage requirements for date and time types
Column type Storage requirements
DATE 3 words Section
DATETIME 8 bytes
TIMESTAMP 4 bytes
TIME 3 bytes
YEAR 1 byte
Storage requirements for string type
Column type Storage requirements
CHAR(M) M bytes, 0 <= M <= 255
VARCHAR(M) L+1 bytes, where L <= M and 0 <= M <= 65535 (see notes below)
BINARY(M) M bytes, 0 < ;= M <= 255
VARBINARY(M) L+1 bytes, where L <= M and 0 <= M <= 255
TINYBLOB, TINYTEXT L +1 bytes, where L < 28
BLOB, TEXT L+2 bytes, where L < 216
MEDIUMBLOB, MEDIUMTEXT L+3 bytes, where L < 224
LONGBLOB, LONGTEXT L+4 bytes, where L < 232
ENUM('value1','value2',…) 1 or 2 bytes, depending Based on the number of enumeration values (up to 65,535 values)
SET('value1','value2',...) 1, 2, 3, 4 or 8 bytes, depending on the number of set members (Maximum 64 members)
VARCHAR, BLOB and TEXT classes are variable-length types. The storage requirements of each type depend on the actual length of the column value (denoted by L in the previous table), not the maximum possible size of the type. For example, a VARCHAR(10) column can hold a string of maximum length 10. The actual storage requirement is the length of the string (L), plus a byte recording the length of the string. For the string 'abcd', L is 4 and storage requires 5 bytes.
For CHAR, VARCHAR, and TEXT types, the values L and M in the previous table should be interpreted as the number of characters, and the length of these types in the column definition represents the number of characters. For example, to save a TINYTEXT value requires L characters +
1 bytes.
To calculate the number of bytes used to save a specific CHAR, VARCHAR, or TEXT column value, you need to consider the character set used by the column. In the specific case, when working with Unicode, you must remember that all Unicode characters use the same number of bytes. For a breakdown of storage used by different classes of Unicode characters, see Section 10.5, “Unicode Support”.
Note: The effective maximum length of a VARCHAR column is 65,532 characters.
NDBCLUSTER engine only supports fixed-width columns. This means that VARCHAR columns in tables in MySQL Cluster behave like type CHAR (except that each record still has an extra byte of space). For example, in a Cluster table, each record in a column declared as VARCHAR(100) will occupy 101 bytes when stored, regardless of the length of the string in the actual stored record.
BLOB and TEXT classes require 1, 2, 3, or 4 bytes to record the length of the column value, depending on the maximum possible length of the class. See Section 11.4.3, “BLOB and TEXT Types
”.
In the NDB Cluster storage engine, the implementation of TEXT and BLOB columns is different, where each record in the TEXT column consists of two separate parts. One is fixed size (256 bytes) and is actually saved in the original table. The other includes any data beyond 256 bytes, held in an implicit table. Records in the second table are always 2,000 bytes long. This means that if size<= 256, the size of the TEXT column is 256 (where size represents the size of the record); otherwise, the size is 256
+size+(2000–(size–256)%2000).
The size of an ENUM object is determined by the number of different enumeration values. The enumeration uses one byte and can have 255 possible values. When the enumeration value is between 256 and 65,535, two bytes are used. See Section 11.4.4, “ENUM Types”.
The size of the SET object is determined by the number of different set members. If the set size is N, the object occupies (N+7)/8 bytes, rounded to 1, 2, 3, 4, or 8 bytes. A SET can have up to 64 members. See Section 11.4.5, “SET Type”.
11.6. Choosing the Correct Column Type
To optimize storage, the most precise type should be used in any case. For example, if the column's values range from 1 to 99999, MEDIUMINT
UNSIGNED is a good type if you use integers. This type uses the least storage of all types that can represent the column value.
Perform all basic calculations (+, -, *, /) on the DECIMAL column with a precision of 65 decimal digits (based on 10). See Section 11.1.1, “Numerical Types Overview”.
Use double precision operations to calculate DECIMAL values. If accuracy is not too important or if speed is the highest priority, the DOUBLE type is sufficient. To achieve high precision, conversion to fixed-point types stored in BIGINT can be performed. This allows all calculations to be done with 64-bit integers, converting the results back to floating point values as needed.
11.7. Using column types from other database engines
In order to use SQL execution code written by other vendors, MySQL maps column types as shown in the following table . These mappings make it easy to import table definitions from other database engines into MySQL:
Other Seller Types MySQL Types
BOOL, TINYINT
BOOLEAN TINYINT
CHAR VARYING(M) VARCHAR(M)
DEC DECIMAL
FIXED DECIMAL
FLOAT4 FLOAT
FLOAT8 DOUBLE
INT1 TINYINT
INT2 SMALLINT
INT3 MEDIUMINT
INT4 INT
INT8 BIGINT
LONG VARBINARY MEDIUMBLOB
LONG VARCHAR MEDIUMTEXT
LONG MEDIUMTEXT
MIDDLEINT MEDIUMINT
NUMERIC DECIMAL
Map the column type when creating the table, and then the original type The definition is discarded. If you create a table using another vendor's type and then execute the DESCRIBE tbl_name statement, MySQL uses the equivalent MySQL type to report the structure of the table.
The above is the content of MySQL Learning Series 3: Data Types. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!