mysql Tutorial-19-data type

老马啸西风2024年1月10日大约 10 分钟

拓展阅读

linq

querydsl

mysql 的 data type

MySQL支持多种数据类型，每种类型都有自己的特点和用途。

以下是MySQL常用的数据类型及其特点的详细介绍：

1. 整数类型（Integer Types）：

TINYINT：范围为-128到127（有符号）或0到255（无符号），占用1字节。
SMALLINT：范围为-32768到32767（有符号）或0到65535（无符号），占用2字节。
MEDIUMINT：范围为-8388608到8388607（有符号）或0到16777215（无符号），占用3字节。
INT（或INTEGER）：范围为-2147483648到2147483647（有符号）或0到4294967295（无符号），占用4字节。
BIGINT：范围为-9223372036854775808到9223372036854775807（有符号）或0到18446744073709551615（无符号），占用8字节。

2. 浮点数类型（Floating-Point Types）：

FLOAT：单精度浮点数，占用4字节。
DOUBLE：双精度浮点数，占用8字节。
DECIMAL（或NUMERIC）：用于高精度的十进制数，需要指定精度和小数位数，占用指定的字节数。

3. 字符串类型（String Types）：

CHAR：固定长度的字符串，最大长度为255字节。
VARCHAR：可变长度的字符串，最大长度为65535字节。
TEXT：长文本字符串，最大长度为65535字符。
BLOB：二进制大对象，用于存储二进制数据，最大长度为65535字节。

4. 日期和时间类型（Date and Time Types）：

DATE：日期，格式为'YYYY-MM-DD'，范围为'1000-01-01'到'9999-12-31'。
TIME：时间，格式为'HH:MM:SS'，范围为'-838:59:59'到'838:59:59'。
DATETIME：日期和时间，格式为'YYYY-MM-DD HH:MM:SS'，范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。
TIMESTAMP：时间戳，格式为'YYYY-MM-DD HH:MM:SS'，范围为'1970-01-01 00:00:01'到'2038-01-19 03:14:07'，以UNIX时间戳格式存储。

5. 其他类型：

ENUM：枚举类型，用于存储一组预定义的字符串值。
SET：集合类型，类似于枚举类型，但可以存储多个值。

6. JSON类型：

JSON：存储JSON格式的数据。

特点：

每种数据类型都有其适用的场景和特点，选择合适的数据类型有助于提高数据库的效率和性能。
在设计数据库时，应根据数据的特点和存储需求选择合适的数据类型，并尽量避免使用过大或过小的数据类型，以节省存储空间和提高查询效率。
此外，还应注意不同数据库引擎对数据类型的支持和限制，在选择数据类型时需要考虑到所使用的数据库引擎的特点。

mysql 的整数类型

说明

MySQL提供了多种整数类型，每种类型具有不同的存储范围和占用空间。以下是MySQL常用的整数类型及其特点的详细介绍：

1. TINYINT：

范围为-128到127（有符号）或0到255（无符号）。
占用1字节。
适用于存储较小范围的整数值，如性别、状态等。

2. SMALLINT：

范围为-32768到32767（有符号）或0到65535（无符号）。
占用2字节。
适用于存储中等范围的整数值，如年龄、数量等。

3. MEDIUMINT：

范围为-8388608到8388607（有符号）或0到16777215（无符号）。
占用3字节。
适用于存储中等范围的整数值，比SMALLINT更大。

4. INT（或INTEGER）：

范围为-2147483648到2147483647（有符号）或0到4294967295（无符号）。
占用4字节。
是MySQL中最常用的整数类型，适用于存储大范围的整数值，如主键、ID等。

5. BIGINT：

范围为-9223372036854775808到9223372036854775807（有符号）或0到18446744073709551615（无符号）。
占用8字节。
适用于存储极大范围的整数值，如时间戳、大数据量的计数器等。

特点：

整数类型在MySQL中用于存储整数值，可指定是否有符号。
有符号整数类型的取值范围为负数到正数，无符号整数类型的取值范围为非负数。
整数类型的占用空间随范围大小而增加，应根据实际需求选择合适的整数类型，以节省存储空间和提高性能。
在选择整数类型时，应根据存储的数据范围和精度选择合适的类型，避免使用过大或过小的整数类型。

例子

下面有一个例子，展示了如何在MySQL中创建表时使用不同的整数类型：

drop table if exists example_table_int;
CREATE TABLE example_table_int (
    tinyint_column TINYINT,
    smallint_column SMALLINT,
    mediumint_column MEDIUMINT,
    int_column INT,
    bigint_column BIGINT
);

INSERT INTO example_table_int (tinyint_column, smallint_column, mediumint_column, int_column, bigint_column)
VALUES 
    (10, 100, 1000, 10000, 1000000),
    (-5, -50, -500, -5000, -5000000);

在这个例子中，我们创建了一个名为example_table_int的表，包含了5个整数类型的列，分别是TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT。

数据效果：

mysql> SELECT * FROM example_table_int;
+----------------+-----------------+------------------+------------+---------------+
| tinyint_column | smallint_column | mediumint_column | int_column | bigint_column |
+----------------+-----------------+------------------+------------+---------------+
|             10 |             100 |             1000 |      10000 |       1000000 |
|             -5 |             -50 |             -500 |      -5000 |      -5000000 |
+----------------+-----------------+------------------+------------+---------------+
2 rows in set (0.00 sec)

mysql 的浮点数类型

说明

MySQL提供了两种常用的浮点数类型：FLOAT和DOUBLE，它们都用于存储近似值数值数据。

以下是对这两种类型的详细介绍：

1. FLOAT：

FLOAT用于存储单精度浮点数。
占用4字节（32位）的存储空间。
能够表示的范围为-3.402823466E+38到-1.175494351E-38、0，以及1.175494351E-38到3.402823466E+38。
在存储非常大或非常小的数值时，FLOAT可能会失去精度。

2. DOUBLE：

DOUBLE用于存储双精度浮点数。
占用8字节（64位）的存储空间。
能够表示的范围为-1.7976931348623157E+308到-2.2250738585072014E-308、0，以及2.2250738585072014E-308到1.7976931348623157E+308。
比FLOAT类型有更高的精度，通常用于存储需要高精度的浮点数数据。

注意事项：

浮点数类型是近似值数值类型，不适用于精确计算。
在进行比较或计算时，应该注意浮点数类型的舍入误差，尤其是在比较小数值时。
在存储非常大或非常小的数值时，应谨慎选择合适的浮点数类型，以避免精度丢失的问题。
在进行大量浮点数计算时，应注意避免累积误差，可以考虑使用DECIMAL类型进行精确计算。

示例：

下面是一个创建表并插入浮点数数据的示例：

drop table if exists example_table_float;
CREATE TABLE example_table_float (
    float_column FLOAT,
    double_column DOUBLE
);

INSERT INTO example_table_float (float_column, double_column)
VALUES 
    (10.12345, 123456.789),
    (-5.6789, -987654.321);

在这个示例中，我们创建了一个名为example_table的表，包含了两个浮点数类型的列，分别是FLOAT和DOUBLE。

mysql> SELECT * FROM example_table_float;
+--------------+---------------+
| float_column | double_column |
+--------------+---------------+
|      10.1235 |    123456.789 |
|      -5.6789 |   -987654.321 |
+--------------+---------------+
2 rows in set (0.00 sec)

mysql 的字符串类型

说明

MySQL提供了多种字符串类型，包括定长字符串和变长字符串。

以下是对MySQL常用字符串类型的详细介绍：

1. CHAR：

CHAR是定长字符串类型，用于存储固定长度的字符串。
最大长度为255个字符。
如果存储的字符串长度小于最大长度，MySQL会在字符串后面添加空格，使其达到最大长度。
适用于长度固定的字符串，如国家代码、性别等。

2. VARCHAR：

VARCHAR是变长字符串类型，用于存储可变长度的字符串。
最大长度为65535个字符。
实际占用的存储空间为实际字符串长度加上1或2个字节的长度前缀。
适用于长度不固定的字符串，如用户名、邮件地址等。

3. TEXT：

TEXT是用于存储长文本字符串的类型。
可存储最大长度为65535个字符。
适用于存储较长的文本内容，如文章、评论等。

4. BLOB：

BLOB是用于存储二进制大对象的类型。
可存储最大长度为65535个字节。
适用于存储二进制数据，如图片、音频、视频等。

5. ENUM：

ENUM是枚举类型，用于存储一组预定义的字符串值。
可以从一个字符串列表中选择一个或多个值作为枚举值。
适用于存储有限的、预定义的字符串值集合，如性别、学历等。

6. SET：

SET是集合类型，用于存储一组预定义的字符串值。
可以从一个字符串列表中选择一个或多个值作为集合元素。
适用于存储有限的、预定义的字符串值集合，与ENUM相比，SET可以选择多个值作为集合元素。

PS: 这里把 ENUM/SET 一起放在这里了，当然也可以单独拆开。

注意事项：

在选择字符串类型时，应根据存储的数据特点和长度选择合适的类型，以节省存储空间并提高性能。
对于经常变动的数据和长度不固定的数据，应使用VARCHAR或TEXT类型；对于长度固定的数据，应使用CHAR类型。
在存储大量文本或二进制数据时，应注意数据库的性能和存储限制，可以考虑使用TEXT或BLOB类型。
使用ENUM和SET类型时，应谨慎考虑预定义的值集合是否足够满足业务需求，并避免频繁修改枚举值集合。

示例

下面是一个创建表并插入字符串数据的示例：

drop table if exists example_table_varchar;
CREATE TABLE example_table_varchar (
    char_column CHAR(10),
    varchar_column VARCHAR(255),
    text_column TEXT,
    blob_column BLOB,
    enum_column ENUM('male', 'female'),
    set_column SET('red', 'green', 'blue')
);

INSERT INTO example_table_varchar (char_column, varchar_column, text_column, blob_column, enum_column, set_column)
VALUES 
    ('老马', '啸西风', '重新学习 mysql', '一步一步来', 'male', 'red,blue');

在这个示例中，我们创建了一个名为example_table的表，包含了不同的字符串类型的列。

SELECT * FROM example_table_varchar;

+-------------+----------------+--------------------+-----------------+-------------+------------+
| char_column | varchar_column | text_column        | blob_column     | enum_column | set_column |
+-------------+----------------+--------------------+-----------------+-------------+------------+
| 老马        | 啸西风         | 重新学习 mysql     | 一步一步来      | male        | red,blue   |
+-------------+----------------+--------------------+-----------------+-------------+------------+

mysql 的日期和时间类型

说明

MySQL提供了多种日期和时间类型，用于存储日期、时间或日期时间数据。

以下是对MySQL常用日期和时间类型的详细介绍：

1. DATE：

DATE用于存储日期数据，格式为'YYYY-MM-DD'。
范围从'1000-01-01'到'9999-12-31'。
占用3个字节的存储空间。
适用于存储年月日信息，如生日、入职日期等。

2. TIME：

TIME用于存储时间数据，格式为'HH:MM:SS'。
范围从'-838:59:59'到'838:59:59'。
占用3个字节的存储空间。
适用于存储时分秒信息，如开会时间、工作时长等。

3. DATETIME：

DATETIME用于存储日期时间数据，格式为'YYYY-MM-DD HH:MM:SS'。
范围从'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。
占用8个字节的存储空间。
适用于存储日期和时间的组合信息，如订单创建时间、日程安排等。

4. TIMESTAMP：

TIMESTAMP也用于存储日期时间数据，格式为'YYYY-MM-DD HH:MM:SS'。
范围从'1970-01-01 00:00:01'到'2038-01-19 03:14:07'。
占用4个字节的存储空间。
默认情况下，TIMESTAMP类型会自动更新为当前时间，可用于记录数据的创建或修改时间。

5. YEAR：

YEAR用于存储年份数据，格式为'YYYY'或'YY'。
范围从1901到2155。
占用1个字节的存储空间。
适用于存储年份信息，如毕业年份、成立年份等。

注意事项：

在选择日期和时间类型时，应根据存储的数据特点和需求选择合适的类型，以节省存储空间和提高性能。
TIMESTAMP类型通常用于记录数据的创建或修改时间，但需要注意其范围和自动更新的特性。
在存储日期和时间数据时，应注意不同的时区和日期格式对数据的影响，并进行适当的处理和转换。
在进行日期和时间计算时，应注意不同类型之间的转换和计算规则，避免出现错误或不一致的结果。

示例

下面是一个创建表并插入日期和时间数据的示例：

CREATE TABLE example_table_time (
    date_column DATE,
    time_column TIME,
    datetime_column DATETIME,
    timestamp_column TIMESTAMP,
    year_column YEAR
);

INSERT INTO example_table_time (date_column, time_column, datetime_column, timestamp_column, year_column)
VALUES 
    ('2024-04-01', '09:30:00', '2024-04-01 09:30:00', CURRENT_TIMESTAMP, '2024');

在这个示例中，我们创建了一个名为example_table的表，包含了不同的日期和时间类型的列。

mysql> SELECT * FROM example_table_time;
+-------------+-------------+---------------------+---------------------+-------------+
| date_column | time_column | datetime_column     | timestamp_column    | year_column |
+-------------+-------------+---------------------+---------------------+-------------+
| 2024-04-01  | 09:30:00    | 2024-04-01 09:30:00 | 2024-04-01 09:57:11 |        2024 |
+-------------+-------------+---------------------+---------------------+-------------+

参考资料

https://www.tutorialspoint.com/mysql/mysql-data-types.htm

贡献者

binbin.hou