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

avatar
作者
猴君
阅读量:0

在这里插入图片描述

文章目录

深入探索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. 电子商务平台

    • 产品信息:使用VARCHARTEXT存储产品名称和描述,使用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() 函数会默认使用,作为分隔符
    • 应用场景:在业务需要输出特定字符串的情况下使用。
  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; 
  1. 社交网络应用

    • 拼接用户名:使用字符串函数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; 
  2. 财务系统

    • 计算利润率:使用数学函数计算销售额和成本之间的比率,帮助分析利润情况。
    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'; 
  3. 内容管理系统

    • 截取摘要内容:使用字符串函数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事务类型

  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. 保证数据完整性

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

四、事物隔离级别类型

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

  1. 读未提交(READ UNCOMMITTED)
    • 特点
      • 最低的隔离级别。
      • 一个事务可以读取另一个事务尚未提交的数据。
      • 可能会出现脏读、不可重复读和幻读。
    • 使用场景
      • 数据一致性要求不高的场景。
      • 适用于快速读取大量数据,但不关心数据准确性的分析任务。
    • 代码Demo
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  START TRANSACTION;  -- 读取操作 SELECT * FROM orders WHERE order_id = 1;  -- 其他事务未提交的更改在此事务中也可见 
  1. 读已提交(READ COMMITTED)
    • 特点
      • 大多数主流数据库的默认隔离级别(如Oracle)。
      • 一个事务只能读取另一个事务已经提交的数据。
      • 避免了脏读,但可能会有不可重复读和幻读。
    • 使用场景
      • 要求避免脏读,但允许不可重复读和幻读的场景。
      • 多数在线交易处理系统(OLTP)使用此级别。
    • 代码Demo
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;  START TRANSACTION;  -- 读取操作 SELECT * FROM orders WHERE order_id = 1;  -- 其他事务提交的更改在此事务中可见 
  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; 
  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); 

五、数据一致性问题类型

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

  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. 不可重复读(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. 幻读(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; 

心如明镜台,时时勤拂拭

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!