深入探索Java开发世界：MySQL~类型分析大揭秘

深入探索Java开发世界：MySQL~类型分析大揭秘

MySQL数据库基础知识，类型知识点梳理~

一、数据结构类型

MySQL是一种关系型数据库管理系统，支持多种数据结构类型，用于存储和管理结构化数据。选择合适的数据类型不仅能提高存储效率，还能提升查询性能和数据完整性。

数据结构类型：

1. 数值类型

   • 整数类型
     • TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
     • 应用场景：用于存储整型数据，如用户ID、计数器、状态标识等。
   • 浮点类型
     • FLOAT, DOUBLE
     • 应用场景：用于存储需要小数精度的数值，如科学计算、财务数据。
   • 定点数类型
     • DECIMAL
     • 应用场景：适用于需要高精度的数值运算，如货币金额存储。

2. 日期和时间类型

   • 日期类型
     • DATE, TIME, DATETIME, TIMESTAMP, YEAR
     • 应用场景：用于存储和操作日期和时间信息，如订单日期、事件时间戳、注册时间等。

3. 字符串类型

   • 字符类型
     • CHAR, VARCHAR
     • 应用场景：用于存储较短的文本数据，如用户名、电子邮件地址、简短描述等。
   • 文本类型
     • TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT
     • 应用场景：用于存储较长的文本数据，如文章内容、评论、日志记录等。
   • 二进制类型
     • BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB
     • 应用场景：用于存储二进制数据，如图片、音频、视频文件等。

4. 枚举和集合类型

   • 枚举类型
     • ENUM
     • 应用场景：用于存储有限的离散值，如性别、状态等。
   • 集合类型
     • SET
     • 应用场景：用于存储多个值的组合，如用户的兴趣爱好等。

5. JSON 类型

   • JSON
   • 应用场景：用于存储和查询JSON格式的数据，适合灵活的数据模型和嵌套结构，如配置数据、复杂对象存储等。

实际案例~应用场景分析：

在开发过程中，根据具体的业务需求和数据特点来选用合适的数据类型，以实现最佳的性能和可维护性。下方举例四种常见的平台来简要分析一下。

1. 电子商务平台

   • 产品信息：使用VARCHAR或TEXT存储产品名称和描述，使用DECIMAL存储价格，使用INT存储库存数量。
   • 订单管理：使用DATETIME记录订单时间，使用ENUM记录订单状态 （如待支付、已支付、已发货等） 。
   • 用户信息：使用VARCHAR存储用户名和电子邮件地址，使用DATE存储用户生日。

2. 社交网络

   • 用户资料：使用VARCHAR存储用户名和密码，使用TEXT存储用户简介，使用DATE存储生日。
   • 帖子和评论：使用TEXT存储帖子和评论内容，使用DATETIME记录发布时间。
   • 好友关系：使用INT存储用户ID，使用SET表示关系类型（好友，关注者，被关注者）。

3. 财务系统

   • 账户信息：使用INT存储账户ID，使用DECIMAL存储余额，使用ENUM表示账户类型（储蓄账户、支票账户）。
   • 交易记录：使用DECIMAL存储交易金额，使用DATETIME记录交易时间，使用ENUM表示交易类型（存款、取款、转账）。

4. 内容管理系统

   • 文章信息：使用TEXT存储文章内容，使用VARCHAR存储标题，使用DATETIME记录发布时间。
   • 分类和标签：使用VARCHAR存储分类和标签名称，使用INT作为关联键。
   • 用户权限：使用ENUM表示用户角色（管理员、编辑、作者、读者）。

二、函数类型

MySQL提供了丰富的函数类型，包括数学函数、日期函数、字符串函数、逻辑函数等。这些函数能在数据库中进行数据处理、转换和计算，提高数据处理的效率和灵活性。

函数类型：

1. 数学函数

   • ABS()：返回一个数的绝对值。
     • 应用场景：在需要获取数值的绝对值时使用，如计算差值或距离。
   • ROUND()：对一个数进行四舍五入。
     • 应用场景：常用于对金额或其他浮点数进行精确的舍入操作。
   • CEIL() 和 FLOOR()：分别向上取整和向下取整。
     • 应用场景：在需要对数值进行向上或向下取整的情况下使用，如对数量、价格进行取整。

2. 日期函数

   • NOW()：返回当前的日期和时间。
     • 应用场景：用于记录当前操作的时间戳，如用户注册时间、订单创建时间等。
   • DATE_FORMAT()：将日期转换为指定的格式。
     • 应用场景：在需要按照特定格式显示日期的情况下使用，如报表生成、数据导出等。
   • DATEDIFF()：计算两个日期之间的天数差。
     • 应用场景：用于计算日期间隔，如计算用户的会员时长、产品的上架时间等。

3. 字符串函数

   • CONCAT()：连接两个或多个字符串。
     • 应用场景：常用于拼接字段值，生成完整的文本内容，如姓名的全名、地址的完整描述等。
   • SUBSTRING()：返回一个字符串的子串。
     • 应用场景：在需要提取部分文本内容的情况下使用，如截取身份证号中的出生日期信息。
   • LOWER() 和 UPPER()：分别将字符串转换为小写和大写。
     • 应用场景：在需要统一字符串大小写格式的情况下使用，如对搜索关键词进行大小写不敏感处理。

• GROUP_CONCAT()：将多行结果集中的值连接成一个字符串，并且可以指定每个字段之间的分隔符。如果不指定分隔符，GROUP_CONCAT() 函数会默认使用,作为分隔符
  • 应用场景：在业务需要输出特定字符串的情况下使用。

4. 逻辑函数
   • IF()：根据条件返回两个值中的一个。
     • 应用场景：在需要根据条件判断返回不同值的情况下使用，如根据用户类型返回不同的折扣率。
   • CASE WHEN THEN ELSE END：多重条件判断和返回。
     • 应用场景：适用于复杂的条件判断和返回逻辑，如根据不同的销售额范围计算不同的奖金。

应用场景分析：

1. 电子商务平台
   • 计算订单总金额：使用数学函数SUM()计算订单中商品的总金额。

   SELECT order_id, SUM(price * quantity) AS total_amount
   FROM order_details
   GROUP BY order_id;
1
2
3

• 格式化日期显示：使用日期函数DATE_FORMAT()将订单时间格式化为可读的日期格式。

SELECT order_id, DATE_FORMAT(order_date, '%Y-%m-%d %H:%i:%s') AS formatted_date
FROM orders;
1
2

2. 社交网络应用

   • 拼接用户名：使用字符串函数CONCAT()拼接用户的姓和名，生成完整的用户名。

   SELECT user_id, CONCAT(first_name, ' ', last_name) AS full_name
   FROM users;
   1
   2

   • 根据用户活跃度判断：使用逻辑函数IF()根据用户最近登录时间判断用户是否活跃。

   SELECT user_id, last_login,
          IF(last_login > DATE_SUB(NOW(), INTERVAL 30 DAY), 'Active', 'Inactive') AS activity_status
   FROM users;
   1
   2
   3

3. 财务系统

   • 计算利润率：使用数学函数计算销售额和成本之间的比率，帮助分析利润情况。

   SELECT product_id, (sales_amount - cost_amount) / sales_amount AS profit_margin
   FROM financials;
   1
   2

   • 根据日期范围筛选数据：使用日期函数比较订单创建时间，筛选出指定日期范围内的交易记录。

   SELECT transaction_id, amount, transaction_date
   FROM transactions
   WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31';
   1
   2
   3

4. 内容管理系统

   • 截取摘要内容：使用字符串函数SUBSTRING()截取文章内容生成摘要展示。

   SELECT article_id, SUBSTRING(content, 1, 200) AS summary
   FROM articles;
   1
   2

   • 根据用户权限控制内容显示：使用逻辑函数CASE WHEN THEN ELSE END根据用户角色控制内容的显示权限。

   SELECT content_id, 
          CASE 
              WHEN user_role = 'admin' THEN full_content
              WHEN user_role = 'editor' THEN editor_content
              ELSE public_content
          END AS display_content
   FROM contents
   JOIN users ON contents.user_id = users.user_id;
   1
   2
   3
   4
   5
   6
   7
   8

三、事物类型

MySQL支持多种事务类型，主要包括非事务型引擎（如MyISAM）和事务型引擎（如InnoDB），并且可以用于处理需要一致性和并发控制的场景，通过保证事务的ACID特性，可以有效地管理数据操作的完整性和可靠性。

MySQL事务类型：

1. 非事务型引擎：
   • MyISAM引擎不支持事务，它的表级锁设计使得在高并发环境下表现较差。适合于读密集、写少的场景，例如简单的查询应用。
2. 事务型引擎：
   • InnoDB引擎支持事务，是MySQL的默认事务引擎。它提供了ACID（原子性、一致性、隔离性、持久性）事务支持，能够处理复杂的并发操作。

应用场景分析：

1. 转账操作：
   • 当需要确保从一个账户扣款并将款项转移到另一个账户时，必须确保这两个操作要么同时成功，要么同时失败，以保持数据的一致性。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TransactionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/transition_demo";
        String user = "root";
        String password = "123456";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            // 开启事务
            conn.setAutoCommit(false); 

            Statement stmt = conn.createStatement();
            // 执行转账操作，更新账户余额
            // 使用两条记录进行模拟
            stmt.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE account_id = 1");
            stmt.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE account_id = 2");

            // 提交事务
            conn.commit(); 
            System.out.println("Transaction committed successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                if (conn != null) {
                    // 回滚事务
                    conn.rollback(); 
                    System.out.println("Transaction rolled back.");
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

2. 批量操作：

   • 当需要在数据库中执行一系列相关联的操作，并且要求这些操作要么全部成功，要么全部失败时，事务非常有用。例如，插入多条相关联的记录或更新多个表中的数据。

3. 保证数据完整性：

   • 当需要确保复杂操作（如订单处理）的一致性时，事务可以保证在操作执行期间其他事务不能读取到未提交的数据，从而避免了脏读和不可重复读等问题。

四、事物隔离级别类型

MySQL支持四种事务隔离级别，每种级别提供的隔离程度不同，适用于不同的应用场景。

1. 读未提交（READ UNCOMMITTED）
   • 特点：
     • 最低的隔离级别。
     • 一个事务可以读取另一个事务尚未提交的数据。
     • 可能会出现脏读、不可重复读和幻读。
   • 使用场景：
     • 数据一致性要求不高的场景。
     • 适用于快速读取大量数据，但不关心数据准确性的分析任务。
   • 代码Demo：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

START TRANSACTION;

-- 读取操作
SELECT * FROM orders WHERE order_id = 1;

-- 其他事务未提交的更改在此事务中也可见
1
2
3
4
5
6
7
8

2. 读已提交（READ COMMITTED）
   • 特点：
     • 大多数主流数据库的默认隔离级别（如Oracle）。
     • 一个事务只能读取另一个事务已经提交的数据。
     • 避免了脏读，但可能会有不可重复读和幻读。
   • 使用场景：
     • 要求避免脏读，但允许不可重复读和幻读的场景。
     • 多数在线交易处理系统（OLTP）使用此级别。
   • 代码Demo：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

-- 读取操作
SELECT * FROM orders WHERE order_id = 1;

-- 其他事务提交的更改在此事务中可见
1
2
3
4
5
6
7
8

3. 可重复读（REPEATABLE READ）
   • ​ 特点：
     • MySQL的默认隔离级别。
     • 保证在同一个事务中多次读取相同数据时结果一致。
     • 防止脏读和不可重复读，但可能会出现幻读。
   • 使用场景：
     • 要求读取一致性高，但允许幻读的场景。
     • 适用于需要高一致性的读取操作，例如报表生成。
   • 代码示例：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;

-- 第一次读取操作
SELECT * FROM orders WHERE order_id = 1;

-- 同一事务中的第二次读取操作，结果与第一次一致
SELECT * FROM orders WHERE order_id = 1;
1
2
3
4
5
6
7
8
9

4. 串行化（SERIALIZABLE）
   • 特点：
     • 最高级别的隔离级别。
     • 强制事务串行执行，完全避免脏读、不可重复读和幻读。
     • 性能开销较大，吞吐量低。
   • 使用场景：
     • 数据一致性要求极高的场景。
     • 适用于金融交易等高一致性要求的系统。

代码示例：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

-- 读取操作
SELECT * FROM orders WHERE order_id = 1;

-- 插入操作将被阻塞，直到当前事务完成
INSERT INTO orders (order_id, amount) VALUES (2, 100);
1
2
3
4
5
6
7
8
9

五、数据一致性问题类型

在数据库事务处理中，脏读、不可重复读和幻读是数据一致性问题的三种经典类型 ，根据具体业务需求和数据一致性要求来选择合适的隔离级别，以平衡性能和数据一致性。

1. 脏读（Dirty Read）
   • 定义：
     • 一个事务读取了另一个事务尚未提交的数据。如果此数据随后被回滚，则第一个事务读取到的数据就是无效的，这种现象称为脏读。
   • 示例分析：
     • 假设有两个事务A和B：
       • 事务A更新某行数据但未提交。
       • 事务B读取了事务A未提交的数据。
       • 事务A回滚了这条更改。
   • 代码Demo：

-- 事务B读取到了事务A未提交的数据，如果事务A最终回滚
-- 则事务B读取的数据就是“脏”的、不可靠的数据
-- 事务A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- 事务B
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

START TRANSACTION;
-- 读取到未提交的数据
SELECT balance FROM accounts WHERE account_id = 1; 

-- 事务A 回
ROLLBACK; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2. 不可重复读（Non-repeatable Read）
   • 定义：
     • 在一个事务中多次读取同一行数据，每次读取的结果可能不同，因为其他事务在两次读取之间修改了该行数据并提交。这种现象称为不可重复读。
   • 示例分析：
     • 假设有两个事务A和B：
       • 事务A读取某行数据。
       • 事务B更新了该行数据并提交。
       • 事务A再次读取该行数据，发现数据已发生变化。
   • 代码Demo：

-- 在READ COMMITTED隔离级别下，事务A可能会在两次读取之间看到不同的值
-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
-- 第一次读取
SELECT balance FROM accounts WHERE account_id = 1; 

-- 事务B
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
COMMIT;

-- 事务A
-- 第二次读取，结果可能不同
SELECT balance FROM accounts WHERE account_id = 1; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3. 幻读（Phantom Read）
   • 定义：
     • 在一个事务中两次执行相同查询时，由于其他事务插入或删除了一些行，使得第二次查询结果集不同。这种现象称为幻读。
   • 示例分析：
     • 假设有两个事务A和B：
       • 事务A执行某查询，得到一组结果。
       • 事务B插入了一些新行，并提交。
       • 事务A再次执行相同查询，结果集包含了事务B插入的新行。
   • 代码Demo：

-- 在REPEATABLE READ隔离级别下，虽然可以防止不可重复读，但幻读仍可能发生
-- 只有在SERIALIZABLE隔离级别下才能完全避免幻读
-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;
-- 第一次读取
SELECT * FROM orders WHERE customer_id = 123; 

-- 事务B
START TRANSACTION;
INSERT INTO orders (order_id, customer_id, amount) VALUES (5, 123, 50);
COMMIT;

-- 事务A
-- 第二次读取，结果集可能不同
SELECT * FROM orders WHERE customer_id = 123;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

心如明镜台，时时勤拂拭