数据库相关的零碎记录

数据库相关的零碎记录

  1. 后端开发与架构
  2. 2015.09.19
  3. 10 min read

一、SQL Mode 设置

查看当前 SQL 模式

为了查看 MySQL 当前使用的 SQL 模式，请执行以下查询：

SELECT @@sql_mode;

修改 SQL 模式

  • 会话级别设置 若要在当前会话中临时修改 SQL 模式，可使用如下命令：
    SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
  • 全局设置 若要永久更改 SQL 模式，需要在 MySQL 的配置文件（如 my.cnf 或 my.ini）中添加以下内容：
    sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

二、修改数据库和表的字符编码

修改整个数据库的编码

ALTER DATABASE nacos_config CHARACTER SET utf8mb4 COLLATE `utf8mb4_general_ci`;

更新指定表的字符集

如果需要更改特定表的字符设置，可使用如下 SQL 语句：

ALTER TABLE config_info CONVERT TO CHARACTER SET utf8mb4 COLLATE `utf8mb4_general_ci`;
-- 其他表类似修改

请根据实际需求对每张表执行相应的命令。

三、存储表情符号支持

为了正确地显示和保存包含特殊字符（如表情）的数据，确保数据库及其表使用兼容的编码类型。一种方法是手动设置：

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

另一种是在连接池配置中指定字符集，例如在 MyBatis 配置文件中添加如下属性：

@Bean(name = "dataSource")
@Primary
public DataSource dataSource() {
    DruidDataSource dataSource = new DruidDataSource();
    //...
    dataSource.setConnectionInitSqls(Arrays.asList("set names utf8mb4"));
}

或直接通过 SQL 语句设置字符集:

SET NAMES 'utf8mb4';

在 MyBatis 映射文件中可以使用 @Update 注解来执行此操作：

<update id="setCharsetToUtf8mb4">
    set names utf8mb4
</update>

四、外网访问 MySQL 服务器

为允许从外部网络访问数据库，需更改 MySQL 用户的 host 属性并调整防火墙设置：

  1. 登录到 MySQL 并更新用户表：
mysql -u root -p1234;
use mysql;

update user set host='%' where user='root';

然后刷新权限设置以使更改生效：

FLUSH PRIVILEGES;

  1. 编辑配置文件 mysqld.cnf 并注释掉或删除 bind-address = 127.0.0.1 行。

  2. 确保 MySQL 的端口（默认为 3306）已开放在防火墙中：

sudo ufw allow 3306/tcp

五、Oracle 和 MySQL 批量处理示例

Oracle 中的批量插入和更新语句

批量插入示例：

<insert id="batchInsertUser" parameterType="java.util.ArrayList">
    INSERT ALL
    <foreach collection="list" item="userList" index="index">
       INTO USERINFO(userid,username) VALUES(#{userList.userid},#{userList.username})
    </foreach>
    SELECT 1 FROM DUAL
</insert>

批量更新示例：

<update id="batchUpdateUser" parameterType="java.util.ArrayList">
   <foreach collection="list" item="userlist" index="index" open="begin" close=";end;" separator=";">
      UPDATE USERINFO T SET
         T.USERID = #{userlist.userid,jdbcType=VARCHAR},
         T.USERNAME = #{userlist.username,jdbcType=VARCHAR}
      WHERE T.USERID = #{userlist.userid,jdbcType=VARCHAR}
   </foreach>
</update>

MySQL 中的批量处理语句

插入示例：

<insert id="batchSave" parameterType="java.util.List">
    MERGE INTO RES_SCHOOL_CLUB t
    USING (
        <foreach collection="list" item="item" index="index" separator="union">
            select #{item.id,jdbcType=VARCHAR} ID,
                   #{item.clsSchoolId,jdbcType=VARCHAR} CLS_SCHOOL_ID,
                   #{item.originSchoolId,jdbcType=VARCHAR} ORIGIN_SCHOOL_ID,
                   #{item.resourceId,jdbcType=VARCHAR} RESOURCE_ID,
                   #{item.clsClubId,jdbcType=VARCHAR} CLS_CLUB_ID,
                   #{item.baseAreaId,jdbcType=VARCHAR} BASE_AREA_ID,
                   #{item.createTime,jdbcType=TIMESTAMP} CREATE_TIME
            from dual
        </foreach>
    ) t1 ON (t.CLS_SCHOOL_ID =  t1.CLS_SCHOOL_ID AND t.RESOURCE_ID = t1.RESOURCE_ID)
    WHEN MATCHED THEN UPDATE SET t.CREATE_TIME = t1.CREATE_TIME
    WHEN NOT MATCHED THEN INSERT(
        ID, CLS_SCHOOL_ID, ORIGIN_SCHOOL_ID, RESOURCE_ID, CLS_CLUB_ID, BASE_AREA_ID, CREATE_TIME
    ) VALUES (
        t1.ID, t1.CLS_SCHOOL_ID, t1.ORIGIN_SCHOOL_ID, t1.RESOURCE_ID, t1.CLS_CLUB_ID, t1.BASE_AREA_ID, t1.CREATE_TIME
)
</insert>

更新示例：

<update id="updateBatchByListStat" parameterType="java.util.Map">
    update la_t_advfinished t1 set
        t1.list_stat='07',
        t1.modify_time=systimestamp where t1.id in(
            <foreach collection="ids" separator="," item="id">'${id}'</foreach>
        )
</update>

删除示例：

<delete id="deleteAttractions" parameterType="java.util.List">
    delete from ATTRACTIONS WHERE id IN (
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    )
</delete>

Oracle 数据库中的主键自增实现

在 Oracle 数据库中，可以使用序列和触发器来模拟自动增长的 ID。首先创建一个序列：

CREATE SEQUENCE seq_log_kl_s
MINVALUE 1
MAXVALUE 99999999999
START WITH 1
INCREMENT BY 1
CACHE 300
ORDER;

然后通过触发器在插入操作时使用序列值来生成新记录的 ID：

CREATE OR REPLACE TRIGGER trg_seq_log_kl_s
BEFORE INSERT ON MS50_LOG.LOG_KNOWLEDGE_STAT
FOR EACH ROW
BEGIN
    SELECT seq_log_kl_s.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

分页计算公式

计算当前页的起始索引：

start = (currentPage - 1) * pageSize

其中，pageSize为每页显示的数据条数；currentPage为要访问的页面编号。

总页数计算方法：

提供 5 种不同的写法来实现总页数的计算功能。假设已知总记录数totalCount与每页大小pageSize，则：

  1. pageCount = (totalCount + pageSize - 1) / pageSize;
  2. pageCount = (totalCount - 1) / pageSize + 1;
  3. pageCount = (int)Math.Ceiling((double)totalCount / pageSize);
  4. pageCount = totalCount%pageSize == 0 ? totalCount/pageSize : totalCount/pageSize + 1;

Oracle 获取前一天和后一天时间：

要获取当前日期的前一天或后一天的时间戳，可使用以下查询语句：

  • 前一天开始时刻：
SELECT to_date(to_char(TRUNC(SYSDATE - 1), 'yyyy-mm-dd') || '00:00:00', 'yyyy-mm-dd hh24:mi:ss')
FROM DUAL;
  • 后一天最后时刻：
SELECT to_date(to_char(TRUNC(SYSDATE + 1) - 1/86400, 'yyyy-mm-dd') || '23:59:59', 'yyyy-mm-dd hh24:mi:ss')
FROM DUAL;

解决 Oracle 驱动程序已过时问题

当遇到oracle.jdbc.driver.OracleDriver is deprecated.警告时，需要将DriverClassName由oracle.jdbc.driver.OracleDriver更新为oracle.jdbc.OracleDriver。例如：

jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=UTC

MySQL 数据库中的批量更新方法

除了常规的 MyBatis 映射语句外，还可以使用REPLACE INTO、INSERT ... ON DUPLICATE KEY UPDATE等方式完成数据集的批量修改任务。这些方法各有优势，适用于不同的应用场景。

  • REPLACE INTO：替换已存在的记录或插入新行。

    REPLACE INTO test_tbl (id,dr) VALUES (1,'2'),(2,'3');

  • INSERT … ON DUPLICATE KEY UPDATE：在遇到重复键时更新现有记录的值，否则插入新数据。

    INSERT INTO test_tbl (id,dr) VALUES (1,'4') ON DUPLICATE KEY UPDATE dr='5';

MySQL 字符串函数

在 MySQL 中，提供了多种字符串处理的内置函数来方便用户操作数据。以下介绍几个常用的字符串处理函数：

1. substring() 截取字符串

  • 功能：用于从指定位置开始截取一定长度的字符串。

    SELECT SUBSTRING('Hello, world!',3); // 输出 'llo, world!'

2. find_in_set(str1, str2)

  • 描述: 查找 str1 在用逗号分隔的 str2 中的位置，返回位置索引。

    SELECT FIND_IN_SET('a', 'a,b,c,d'); // 输出 1

3. locate(substr, str)

  • 功能：如果字符串包含子串，则返回大于 0 的位置值；否则返回 0。
UPDATE site SET url = CONCAT('http://',url) WHERE LOCATE('http://', url)=0;

以上代码用于检查 site 表中的 URL 字段是否含有’http://‘，如果没有则在该字段开头添加”http://“前缀。

查看 MySQL 读取配置文件的顺序

如果想查看 MySQL 服务器启动时使用的配置文件路径列表，可以使用以下命令：

/usr/local/mysql/bin/mysqld --verbose --help | grep -A 1 'Default options'

此命令将列出 MySQL 默认加载的所有选项及其对应的值。

MySQL 数据库本地文件位置

MySQL 在 Linux 系统上安装时会在/usr/local/var/mysql目录下创建数据库的数据文件，其中：

  • .frm 文件包含表结构定义。
  • .ibd 文件是实际存储数据的文件。

获取数据库驱动 Class.forName()

在 Java 中使用 JDBC 连接到 MySQL 等数据库时，通常会用到 Class.forName() 方法来加载数据库驱动类。此方法用于动态地将指定的类（如 MySQL JDBC 驱动）加载进 Java 虚拟机 (JVM)，并执行静态块内的初始化代码。

  • 返回值：
    • Class.forName("") 返回的是指定名称对应的类对象。
    • Class.forName("").newInstance() 返回一个新的实例，如果该类有公共的无参数构造器。

MySQL 驱动实现

MySQL JDBC 驱动通过继承 NonRegisteringDriver 类并实现 java.sql.Driver 接口来注册自身到 DriverManager ，从而可以在应用程序中使用它。

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
        super();
    }

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can\'t register driver!");
        }
    }
}

Java 调用存储过程

使用 CallableStatement 调用数据库中的存储过程，如下代码所示：

// 获取 CallableStatement 对象
CallableStatement c = con.prepareCall("{call getCustomerName(?,?)}");
c.setString(1, "1"); // 设置参数 1 的值为字符串 '1'
c.registerOutParameter(2, java.sql.Types.VARCHAR); // 注册第二个参数为输出类型
c.execute(); // 执行存储过程

// 获取执行结果的返回值，这里是 VARCHAR 类型
String result = c.getString(2);

MySQL 日期时间函数

MySQL 提供了多种日期时间函数，用于处理和操作日期和时间。以下是一些常用的日期时间函数：

  • NOW()：返回当前日期和时间。

    SELECT NOW(); // 输出 '2023-10-01 12:34:56'

  • CURDATE()：返回当前日期。

    SELECT CURDATE(); // 输出 '2023-10-01'

  • CURTIME()：返回当前时间。

数据库 学习笔记 经验总结