MySQL5.7数据类型详解

需积分: 10 75 浏览量更新于2024-07-20 收藏 3.57MB PDF 举报

"MySQL5.7-data_type" 涉及到MySQL数据库系统中的数据类型，这是数据库设计的基础，用于定义存储在表列中的数据的种类、格式和范围。本章详细介绍了MySQL5.7中各种数据类型的概述、特点以及使用场景。 12.1 数据类型概述这一部分为读者提供了数据类型的总体概念，包括数值类型、日期和时间类型以及字符串类型。它是理解后续详细分类的入口。 12.1.1 数值类型概述数值类型包括精确值和近似值两大类，如整型（INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT）、固定点类型（DECIMAL, NUMERIC）和浮点类型（FLOAT, DOUBLE）。这些类型用于存储数学计算中的数字，每种类型有其特定的存储范围和精度。 12.1.2 日期和时间类型概述日期和时间类型用于处理日期、时间和日期时间组合的数据，如DATE, DATETIME, TIMESTAMP, TIME, 和 YEAR。它们在日志记录、事件追踪等场景中至关重要。 12.1.3 字符串类型概述字符串类型涵盖了CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM以及SET等，用于存储文本数据，各有其长度限制和特性，如定长和变长、二进制和非二进制的区别。 12.2 数值类型这部分深入讲解了各种数值类型，包括整型的详细存储规格、固定点类型在精度上的控制，以及浮点类型在近似计算中的应用。此外，还提到了BIT类型，用于存储位数据。 12.2.5 数值类型属性这部分介绍数值类型的相关属性，如精度、尺度等，以及超出范围和溢出处理机制。 12.3 日期和时间类型详细阐述了各种日期和时间类型的使用，包括DATE, DATETIME, TIMESTAMP的格式和用途，TIME表示时间的处理，YEAR类型以及它的限制和迁移问题。此外，还涉及到TIMESTAMP和DATETIME的自动初始化和更新，时间值中的分数秒，日期时间类型之间的转换，以及两位数年份的处理。 12.4 字符串类型深入探讨了不同类型的字符串，如定长和变长的字符和二进制类型，大对象（BLOB和TEXT）用于存储大量文本，以及枚举（ENUM）和集合（SET）类型，它们提供了预定义的值列表和多值存储能力。总结，MySQL5.7的数据类型覆盖了各种可能的数据需求，从精确数值到日期时间，再到文本和二进制数据，每个类型都有其特定的设计目的和使用场景。了解这些数据类型对于有效地设计数据库表结构和优化查询性能至关重要。在实际应用中，应根据数据的特性和业务需求选择合适的数据类型。

Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC

1546

Type Storage Minimum Value Maximum Value

(Bytes) (Signed/Unsigned) (Signed/Unsigned)

0 4294967295

BIGINT 8 -9223372036854775808 9223372036854775807

0 18446744073709551615

12.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it

is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is

implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC.

MySQL stores DECIMAL values in binary format. See Section 13.21, “Precision Math”.

In a DECIMAL column declaration, the precision and scale can be (and usually is) specified; for example:

salary DECIMAL(5,2)

In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant

digits that are stored for values, and the scale represents the number of digits that can be stored following

the decimal point.

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals,

so values that can be stored in the salary column range 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), where the implementation is permitted to decide the value of M. MySQL

supports both of these variant forms of DECIMAL syntax. The default value of M is 10.

If the scale is 0, DECIMAL values contain no decimal point or fractional part.

The maximum number of digits for DECIMAL is 65, but the actual range for a given DECIMAL column can

be constrained by the precision or scale for a given column. When such a column is assigned a value with

more digits following the decimal point than are permitted by the specified scale, the value is converted to

that scale. (The precise behavior is operating system-specific, but generally the effect is truncation to the

permissible number of digits.)

12.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for

single-precision values and eight bytes for double-precision values.

For FLOAT, the SQL standard permits an optional specification of the precision (but not the range of the

exponent) in bits following the keyword FLOAT in parentheses. MySQL also supports this optional precision

specification, but the precision value is used only to determine storage size. A precision from 0 to 23

results in a 4-byte single-precision FLOAT column. A precision from 24 to 53 results in an 8-byte double-

precision DOUBLE column.

MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here,

(M,D) means than values can be stored with up to M digits in total, of which D digits may be after the

decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed.

MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column,

the approximate result is 999.0001.

Bit-Value Type - BIT

1547

Because floating-point values are approximate and not stored as exact values, attempts to treat them

as exact in comparisons may lead to problems. They are also subject to platform or implementation

dependencies. For more information, see Section B.5.4.8, “Problems with Floating-Point Values”

For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or

DOUBLE PRECISION with no specification of precision or number of digits.

12.2.4 Bit-Value Type - BIT

The BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can

range from 1 to 64.

To specify bit values, b'value' notation can be used. value is a binary value written using zeros and

ones. For example, b'111' and b'10000000' represent 7 and 128, respectively. See Section 10.1.6,

“Bit-Field Literals”.

If you assign a value to a BIT(M) column that is less than M bits long, the value is padded on the left with

zeros. For example, assigning a value of b'101' to a BIT(6) column is, in effect, the same as assigning

b'000101'.

MySQL Cluster. The maximum combined size of all BIT columns used in a given NDB table must not

exceed 4096 bits.

12.2.5 Numeric Type Attributes

MySQL supports an extension for optionally specifying the display width of integer data types in

parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display

width of four digits. This optional display width may be used by applications to display integer values

having a width less than the width specified for the column by left-padding them with spaces. (That is, this

width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

The display width does not constrain the range of values that can be stored in the column. Nor does it

prevent values wider than the column display width from being displayed correctly. For example, a column

specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the

range permitted by three digits are displayed in full using more than three digits.

When used in conjunction with the optional (nonstandard) attribute ZEROFILL, the default padding of

spaces is replaced with zeros. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is

retrieved as 0005.

Note

The ZEROFILL attribute is ignored when a column is involved in expressions or

UNION queries.

If you store values larger than the display width in an integer column that has

the ZEROFILL attribute, you may experience problems when MySQL generates

temporary tables for some complicated joins. In these cases, MySQL assumes that

the data values fit within the column display width.

All integer types can have an optional (nonstandard) attribute UNSIGNED. Unsigned type can be used

to permit only nonnegative numbers in a column or when you need a larger upper numeric range for the

column. For example, if an INT column is UNSIGNED, the size of the column's range is the same but its

endpoints shift from -2147483648 and 2147483647 up to 0 and 4294967295.

Out-of-Range and Overflow Handling

1548

Floating-point and fixed-point types also can be UNSIGNED. As with integer types, this attribute prevents

negative values from being stored in the column. Unlike the integer types, the upper range of column

values remains the same.

If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the

column.

Integer or floating-point data types can have the additional attribute AUTO_INCREMENT. When you insert

a value of NULL into an indexed AUTO_INCREMENT column, the column is set to the next sequence

value. Typically this is value+1, where value is the largest value for the column currently in the table.

(AUTO_INCREMENT sequences begin with 1.)

Storing 0 into an AUTO_INCREMENT column has the same effect as storing NULL, unless the

NO_AUTO_VALUE_ON_ZERO SQL mode is enabled.

Inserting NULL to generate AUTO_INCREMENT values requires that the column be declared NOT NULL.

If the column is declared NULL, inserting NULL stores a NULL. When you insert any other value into an

AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next

automatically generated value follows sequentially from the inserted value.

In MySQL 5.7, negative values for AUTO_INCREMENT columns are not supported.

12.2.6 Out-of-Range and Overflow Handling

When MySQL stores a value in a numeric column that is outside the permissible range of the column data

type, the result depends on the SQL mode in effect at the time:

• If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in

accordance with the SQL standard.

• If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and

stores the resulting value instead.

When an out-of-range value is assigned to an integer column, MySQL stores the value representing the

corresponding endpoint of the column data type range. If you store 256 into a TINYINT or TINYINT

UNSIGNED column, MySQL stores 127 or 255, respectively.

When a floating-point or fixed-point column is assigned a value that exceeds the range implied by the

specified (or default) precision and scale, MySQL stores the value representing the corresponding

endpoint of that range.

Column-assignment conversions that occur due to clipping when MySQL is not operating in strict mode

are reported as warnings for ALTER TABLE, LOAD DATA INFILE, UPDATE, and multiple-row INSERT

statements. In strict mode, these statements fail, and some or all the values will not be inserted or

changed, depending on whether the table is a transactional table and other factors. For details, see

Section 6.1.7, “Server SQL Modes”.

Overflow during numeric expression evaluation results in an error. For example, the largest signed BIGINT

value is 9223372036854775807, so the following expression produces an error:

mysql> SELECT 9223372036854775807 + 1;

ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'

To enable the operation to succeed in this case, convert the value to unsigned;

mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;

Date and Time Types

1549

+-------------------------------------------+

| CAST(9223372036854775807 AS UNSIGNED) + 1 |

+-------------------------------------------+

| 9223372036854775808 |

+-------------------------------------------+

Whether overflow occurs depends on the range of the operands, so another way to handle the preceding

expression is to use exact-value arithmetic because DECIMAL values have a larger range than integers:

mysql> SELECT 9223372036854775807.0 + 1;

+---------------------------+

| 9223372036854775807.0 + 1 |

+---------------------------+

| 9223372036854775808.0 |

+---------------------------+

Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by

default. If the result would otherwise have been negative, an error results:

mysql> SET sql_mode = '';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

If the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative:

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

mysql> SELECT CAST(0 AS UNSIGNED) - 1;

+-------------------------+

| CAST(0 AS UNSIGNED) - 1 |

+-------------------------+

| -1 |

+-------------------------+

If the result of such an operation is used to update an UNSIGNED integer column, the result is clipped to the

maximum value for the column type, or clipped to 0 if NO_UNSIGNED_SUBTRACTION is enabled. If strict

SQL mode is enabled, an error occurs and the column remains unchanged.

12.3 Date and Time Types

The date and time types for representing temporal values are DATE, TIME, DATETIME, TIMESTAMP, and

YEAR. Each temporal type has a range of valid values, as well as a “zero” value that may be used when

you specify an invalid value that MySQL cannot represent. The TIMESTAMP type has special automatic

updating behavior, described later. For temporal type storage requirements, see Section 12.8, “Data Type

Storage Requirements”.

Keep in mind these general considerations when working with date and time types:

• MySQL retrieves values for a given date or time type in a standard output format, but it attempts to

interpret a variety of formats for input values that you supply (for example, when you specify a value to

be assigned to or compared to a date or time type). For a description of the permitted formats for date

and time types, see Section 10.1.3, “Date and Time Literals”. It is expected that you supply valid values.

Unpredictable results may occur if you use values in other formats.

• Although MySQL tries to interpret values in several formats, date parts must always be given in year-

month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year

orders commonly used elsewhere (for example, '09-04-98', '04-09-98').

剩余71页未读，继续阅读

trayvontang

粉丝: 579
资源: 43

MySQL5.7数据类型详解

mysql5.7-实验操作文档.docx

mysql-8.0.20-macos10.15-x86_64.tar.gz

CentOS-二进制安装mysql5.7

MySQL 5.7 mysql command line client 使用命令详解

MySQL5.7Window安装版安装教程.doc

mysql5.7 生成列 generated column用法实例分析

Navicat Premium Data Transfer Source Server : mysql Source Server Type : MySQL Source Server Version : 50738 (5.7.38-log) Source Host : localhost:3306 Source Schema : wechat_take_photo Target Server Type : MySQL Target Server Version : 50738 (5.7.38-log) File Encoding : 65001什么意思

mysql5.7安装配置教程linux

mysql5.7有哪些伪列

在docker swarm运行mysql 5.7

最新资源