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

文章目录

深入探索Java开发世界：MySQL~类型分析大揭秘
- 一、数据结构类型
- 二、函数类型
- 三、事物类型
- 四、事物隔离级别类型
- 五、数据一致性问题类型
  深入探索Java开发世界：MySQL~类型分析大揭秘
  
  MySQL数据库基础知识，类型知识点梳理~
  
  一、数据结构类型
  
  MySQL是一种关系型数据库管理系统，支持多种数据结构类型，用于存储和管理结构化数据。选择合适的数据类型不仅能提高存储效率，还能提升查询性能和数据完整性。
  
  数据结构类型：
  1. 数值类型
    - 整数类型
      - TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
      - 应用场景：用于存储整型数据，如用户ID、计数器、状态标识等。
      - 浮点类型
        FLOAT, DOUBLE
        应用场景：用于存储需要小数精度的数值，如科学计算、财务数据。
        定点数类型
        DECIMAL
        应用场景：适用于需要高精度的数值运算，如货币金额存储。
        
        日期和时间类型
        
        日期类型
        DATE, TIME, DATETIME, TIMESTAMP, YEAR
        应用场景：用于存储和操作日期和时间信息，如订单日期、事件时间戳、注册时间等。
        
        字符串类型
        
        字符类型
        CHAR, VARCHAR
        应用场景：用于存储较短的文本数据，如用户名、电子邮件地址、简短描述等。
        文本类型
        TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT
        应用场景：用于存储较长的文本数据，如文章内容、评论、日志记录等。
        二进制类型
        BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB
        应用场景：用于存储二进制数据，如图片、音频、视频文件等。
        
        枚举和集合类型
        
        枚举类型
        ENUM
        应用场景：用于存储有限的离散值，如性别、状态等。
        集合类型
        SET
        应用场景：用于存储多个值的组合，如用户的兴趣爱好等。
        
        JSON 类型
        
        JSON
        应用场景：用于存储和查询JSON格式的数据，适合灵活的数据模型和嵌套结构，如配置数据、复杂对象存储等。
  实际案例~应用场景分析：
  
  在开发过程中，根据具体的业务需求和数据特点来选用合适的数据类型，以实现最佳的性能和可维护性。下方举例四种常见的平台来简要分析一下。
  1. 电子商务平台
    - 产品信息：使用VARCHAR或TEXT存储产品名称和描述，使用DECIMAL存储价格，使用INT存储库存数量。
    - 订单管理：使用DATETIME记录订单时间，使用ENUM记录订单状态（如待支付、已支付、已发货等）。
    - 用户信息：使用VARCHAR存储用户名和电子邮件地址，使用DATE存储用户生日。
    - 社交网络
      - 用户资料：使用VARCHAR存储用户名和密码，使用TEXT存储用户简介，使用DATE存储生日。
      - 帖子和评论：使用TEXT存储帖子和评论内容，使用DATETIME记录发布时间。
      - 好友关系：使用INT存储用户ID，使用SET表示关系类型（好友，关注者，被关注者）。
      - 财务系统
        
        账户信息：使用INT存储账户ID，使用DECIMAL存储余额，使用ENUM表示账户类型（储蓄账户、支票账户）。
        交易记录：使用DECIMAL存储交易金额，使用DATETIME记录交易时间，使用ENUM表示交易类型（存款、取款、转账）。
        
        内容管理系统
        
        文章信息：使用TEXT存储文章内容，使用VARCHAR存储标题，使用DATETIME记录发布时间。
        分类和标签：使用VARCHAR存储分类和标签名称，使用INT作为关联键。
        用户权限：使用ENUM表示用户角色（管理员、编辑、作者、读者）。
  二、函数类型
  
  MySQL提供了丰富的函数类型，包括数学函数、日期函数、字符串函数、逻辑函数等。这些函数能在数据库中进行数据处理、转换和计算，提高数据处理的效率和灵活性。
  
  函数类型：
  1. 数学函数
    - ABS()：返回一个数的绝对值。
      - 应用场景：在需要获取数值的绝对值时使用，如计算差值或距离。
      - ROUND()：对一个数进行四舍五入。
        应用场景：常用于对金额或其他浮点数进行精确的舍入操作。
        CEIL() 和 FLOOR()：分别向上取整和向下取整。
        应用场景：在需要对数值进行向上或向下取整的情况下使用，如对数量、价格进行取整。
        
        日期函数
        
        NOW()：返回当前的日期和时间。
        应用场景：用于记录当前操作的时间戳，如用户注册时间、订单创建时间等。
        DATE_FORMAT()：将日期转换为指定的格式。
        应用场景：在需要按照特定格式显示日期的情况下使用，如报表生成、数据导出等。
        DATEDIFF()：计算两个日期之间的天数差。
        应用场景：用于计算日期间隔，如计算用户的会员时长、产品的上架时间等。
        
        字符串函数
        
        CONCAT()：连接两个或多个字符串。
        应用场景：常用于拼接字段值，生成完整的文本内容，如姓名的全名、地址的完整描述等。
        SUBSTRING()：返回一个字符串的子串。
        应用场景：在需要提取部分文本内容的情况下使用，如截取身份证号中的出生日期信息。
        LOWER() 和 UPPER()：分别将字符串转换为小写和大写。
        应用场景：在需要统一字符串大小写格式的情况下使用，如对搜索关键词进行大小写不敏感处理。
  - GROUP_CONCAT()：将多行结果集中的值连接成一个字符串，并且可以指定每个字段之间的分隔符。如果不指定分隔符，GROUP_CONCAT() 函数会默认使用,作为分隔符
    - 应用场景：在业务需要输出特定字符串的情况下使用。
      1. 逻辑函数
        IF()：根据条件返回两个值中的一个。
        应用场景：在需要根据条件判断返回不同值的情况下使用，如根据用户类型返回不同的折扣率。
        CASE WHEN THEN ELSE END：多重条件判断和返回。
        应用场景：适用于复杂的条件判断和返回逻辑，如根据不同的销售额范围计算不同的奖金。
      应用场景分析：
      1. 电子商务平台
        计算订单总金额：使用数学函数SUM()计算订单中商品的总金额。
```
 SELECT order_id, SUM(price * quantity) AS total_amount
   FROM order_details
   GROUP BY order_id;
```
      - 格式化日期显示：使用日期函数DATE_FORMAT()将订单时间格式化为可读的日期格式。
        SELECT order_id, DATE_FORMAT(order_date, '%Y-%m-%d %H:%i:%s') AS formatted_date FROM orders;
        
        社交网络应用
        
        拼接用户名：使用字符串函数CONCAT()拼接用户的姓和名，生成完整的用户名。
        SELECT user_id, CONCAT(first_name, ' ', last_name) AS full_name FROM users;
        
        根据用户活跃度判断：使用逻辑函数IF()根据用户最近登录时间判断用户是否活跃。
        SELECT user_id, last_login, IF(last_login > DATE_SUB(NOW(), INTERVAL 30 DAY), 'Active', 'Inactive') AS activity_status FROM users;
        
        财务系统
        
        计算利润率：使用数学函数计算销售额和成本之间的比率，帮助分析利润情况。
        SELECT product_id, (sales_amount - cost_amount) / sales_amount AS profit_margin FROM financials;
        
        根据日期范围筛选数据：使用日期函数比较订单创建时间，筛选出指定日期范围内的交易记录。
        SELECT transaction_id, amount, transaction_date FROM transactions WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31';
        
        内容管理系统
        
        截取摘要内容：使用字符串函数SUBSTRING()截取文章内容生成摘要展示。
        SELECT article_id, SUBSTRING(content, 1, 200) AS summary FROM articles;
        
        根据用户权限控制内容显示：使用逻辑函数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;
        
        三、事物类型
        
        MySQL支持多种事务类型，主要包括非事务型引擎（如MyISAM）和事务型引擎（如InnoDB），并且可以用于处理需要一致性和并发控制的场景，通过保证事务的ACID特性，可以有效地管理数据操作的完整性和可靠性。
        
        MySQL事务类型：
        
        非事务型引擎：
        MyISAM引擎不支持事务，它的表级锁设计使得在高并发环境下表现较差。适合于读密集、写少的场景，例如简单的查询应用。
        事务型引擎：
        InnoDB引擎支持事务，是MySQL的默认事务引擎。它提供了ACID（原子性、一致性、隔离性、持久性）事务支持，能够处理复杂的并发操作。
        
        应用场景分析：
        
        转账操作：
        当需要确保从一个账户扣款并将款项转移到另一个账户时，必须确保这两个操作要么同时成功，要么同时失败，以保持数据的一致性。
        
        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(); } } } }
        
        批量操作：
        
        当需要在数据库中执行一系列相关联的操作，并且要求这些操作要么全部成功，要么全部失败时，事务非常有用。例如，插入多条相关联的记录或更新多个表中的数据。
        
        保证数据完整性：
        
        当需要确保复杂操作（如订单处理）的一致性时，事务可以保证在操作执行期间其他事务不能读取到未提交的数据，从而避免了脏读和不可重复读等问题。
        
        四、事物隔离级别类型
        
        MySQL支持四种事务隔离级别，每种级别提供的隔离程度不同，适用于不同的应用场景。
        
        读未提交（READ UNCOMMITTED）
        特点：
        最低的隔离级别。
        一个事务可以读取另一个事务尚未提交的数据。
        可能会出现脏读、不可重复读和幻读。
        使用场景：
        数据一致性要求不高的场景。
        适用于快速读取大量数据，但不关心数据准确性的分析任务。
        代码Demo：
        
        SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION; -- 读取操作 SELECT * FROM orders WHERE order_id = 1; -- 其他事务未提交的更改在此事务中也可见
        
        读已提交（READ COMMITTED）
        特点：
        大多数主流数据库的默认隔离级别（如Oracle）。
        一个事务只能读取另一个事务已经提交的数据。
        避免了脏读，但可能会有不可重复读和幻读。
        使用场景：
        要求避免脏读，但允许不可重复读和幻读的场景。
        多数在线交易处理系统（OLTP）使用此级别。
        代码Demo：
        
        SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; -- 读取操作 SELECT * FROM orders WHERE order_id = 1; -- 其他事务提交的更改在此事务中可见
        
        可重复读（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;
        
        串行化（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);
        
        五、数据一致性问题类型
        
        在数据库事务处理中，脏读、不可重复读和幻读是数据一致性问题的三种经典类型，根据具体业务需求和数据一致性要求来选择合适的隔离级别，以平衡性能和数据一致性。
        
        脏读（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;
        
        不可重复读（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;
        
        幻读（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;
        
        心如明镜台，时时勤拂拭

分类:热门推荐日期:2024-06-24浏览:1评论:0

文章目录

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

一、数据结构类型

二、函数类型

三、事物类型

四、事物隔离级别类型

五、数据一致性问题类型

事务 类型

相关推荐