Alive Note - 数据库
type: channel
link: https://vae.li/tag/database/
items: DataGrip 设置时区
items: Python 使用 sqlalchemy 链接 openGauss（高斯）出错解决
items: 【知识点】常见的SQL优化手段
items: 数据库 Tips
items: Sharding-JDBC学习记录
items: Spring Data Jpa 分表处理
items: Oracle 生僻字处理(mybatis、mybatis-plus、通用mapper)
items: Spring动态数据源
items: Spring中@Transactional失效问题
items: MongoDB基础

DataGrip 设置时区
type: item
date: 2025-09-19T05:53:47+00:00
description: 在使用 DataGrip 通过自定义驱动链接人大金仓 kingbase R9 数据库时，出现了时间不对的问题，select now()查询出的时间比北京时间少 8 小时，并且通过show timezone查询出的时区也是UTC。但是在数据库的设置文件kingbase.conf中，已经设置了时区为Asia/Shanghai，以及使用官方工具 KSutdio 2.0 进行链接时，时间和时区也是对的。一番搜索后发现是 DataGrip(以及 IDEA 中 Database 插件) 工具自身的连接方式有问题。修改方式新版本DataGrip以默认时区取世界标准时间。要想时间显示正常，需要将时区变为上海时区，可手动在连接配置里设置参数。如下图：操作步骤：右键打开你想要修改的数据库连接的 Properties 菜单；点击 Advanced 按钮；在 VM options 后面写入-Duser.timezone=Asia/Shanghai，就可以啦；断开数据库连接，重新执行一条SQL（也可刷新数据库）就可以看到效果了。设置方式转自：DataGrip设置时区

Python 使用 sqlalchemy 链接 openGauss（高斯）出错解决
type: item
date: 2025-04-09T09:22:00+00:00
description: sqlalchemy 有版本验证，需要 hack 获取版本的函数：from sqlalchemy.dialects.postgresql.base import PGDialect # 直接返回 9.2（高斯基于 postgresql 9.2.4 版本） PGDialect._get_server_version_info = lambda *args: (9, 2)stackoverflow 地址：SQLAlchemy raises an exception for unknown version while connecting to GaussDB (for openGauss)

【知识点】常见的SQL优化手段
type: item
date: 2024-04-15T09:37:28+00:00
description: 问：有哪些常见的SQL优化手段？这也是个高频面试题，并且并非面试造火箭的那种问题，实际项目中也会有非常多的地方需要进行SQL优化避免使用 Select *select *中，无用字段会增加网络带宽消耗，特别是varchar、blob、text等大字段select *无法使用Mysql优化器覆盖索引的优化。覆盖索引：即一个查询可以完全通过索引来满足，不需要回表回表：根据非聚集索引查询到主键，然后根据主键查询其他数据。分页优化Mysql分页时，并不是跳过 offset，而是取 offset + N 行，然后丢弃 offset行，只返回N行。并且，offset 越大、N 越大、select出的字段越多，耗时就越久。数据量少时，分页耗时还是比较少的。但是上数据量上百万甚至千万的时候，直接limit 1000000, 20是非常慢的。优化方案：先根据排序规则及limit条件查询出主键，然后根据主键关联到表本身。即把select t.* form the_table where 条件 limit 1000000, 20 修改为：select t1.* from the_table t1, (select id from the_table where 条件 limit 1000000, 20) as t2 where t1.id = t2.id并且条件种的字段尽量创建了索引。尽量避免多表Joinjoin 关联的效率不算很高，多个表关联时会使用嵌套循环，如果关联字段没有索引，会直接使用笛卡尔积实现 join，导致全表扫描，效率很低。两种解决方式：单表查询，根据查询结果进行二次查询，最后在 Service 层进行关联。数据冗余：把重要字段冗余在表中，尽可能避免关联查询。推荐使用第一种方式，因为数据库的计算资源更加宝贵，Service层可以很方便的水平扩展。并且如果数据量非常大的时候也会进行分库分表，这也会限制join。建议不要使用外键与级联外键概念应该在应用层解决。选择合适的字段类型存储字节越小，占用空间就越小，性能也就越好。尽量使用 union all 代替 unionunion会把结果集进行去重操作，更耗时。批量操作操作数据时，能批量操作尽量批量处理，减少请求数据库的次数。使用Explain分析SQL正确使用索引适合作为索引的字段不为null的字段：索引字段应尽量不为null，对于数据为null的字段，数据库比较难优化。如果字段频繁被查询，又无法避免为null，可以使用-1、0、true、false等语义清晰地短字符作为代替。被频繁查询的字段被作为条件查询的字段频繁需要排序的字段频繁用于外连接的字段被频繁更新的字段应慎重建立索引虽然索引能带来查询上的效率，但是维护索引的成本也不小。 如果字段不经常被查询，反而经常被修改，那不应该在这种字段上创建索引。尽可能创建联合索引，而不是单列索引索引需要占用空间，如果是联合索引，多个字段在一个索引上，可以节省空间，并且修改数据时效率也会提升。避免冗余索引考虑在字符串类型上使用前缀索引代替普通索引前缀索引只适用于字符串类型，比普通索引占用更少的空间避免索引失效使用 select *查询创建了联合索引，但是没有遵循最左匹配原则在索引列上进行计算、函数、类型转换等操作以%开头的like查询，比如like '%abc'发生隐式转换查询时使用条件1 or 条件2，如果条件1或者条件2中存在未创建索引的字段，就不会使用索引

数据库 Tips
type: item
date: 2024-02-28T08:26:00+00:00
description: 数据库 Tips时间和日期互转数据库当前时间字符串转时间时间转字符串格式dual 表MySQLNOW()STR_TO_DATEDATE_FORMAT%Y-%m-%d %H:%i:%s可选OracleSYSTIMESTAMP、SYSDATETO_TIMESTAMP、TO_DATETO_CHARYYYY-MM-DD HH24:MI:SS必须用PostgreSQLCURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMPTO_DATE、TO_TIMESTAMPTO_CHARYYYY-MM-DD HH24:MI:SS不能用数据库迁移迁移到 PostgreSQL可以使用 pgloader，有 Docker 镜像：dimitri/pgloader图形化工具a5m2：全称「A5:SQL MK-2」，一个日本人开发的图形化工具，直接在数据库上右键，然后选择Data Transfer...就可以了，可以选择不同的数据库类型。navicat：只能迁移相同类型的数据库，比如 mysql -> mysql，或者 pgsql -> pgsql。MySQL创建数据库并指定字符集并不难，只不过用的比较多，记下来CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;时间和字符串互转STR_TO_DATE和DATE_FORMAT，转换格式：%Y-%m-%d %H:%i:%sOracle(+) 符号这种是旧写法，oracle 已经不再推荐详情可见https://stackoverflow.com/a/47008794/17746262在 Oracle 中，(+) 表示 JOIN 中的“可选”表。SELECT a.id, b.id, a.col_2, b.col_2, ... FROM a,b WHERE a.id=b.id(+)现代写法就是：SELECT a.id, b.id, a.col_2, b.col_2, ... FROM a LEFT JOIN b ON a.id=b.id创建序列-- 创建序列 create sequence seq_newsId -- 删除序列 DROP SEQUENCE seq_newsIdPostgreSQL判断时间重合使用tsrange函数：select * from example where tsrange(start_time, end_time, '()') && tsrange(start_time2, end_time2, '()')解释：tsrange 可以把时间转换成时间段然后使用&&判断是否重合，第三个参数代表边界范围，即开区间还是闭区间，可选(和[，前者代表开区间，即不包含边界，后者代表闭区间，包含边界，第一个括号代表开始时间，第二个括号代表结束时间。持续更新中……

Sharding-JDBC学习记录
type: item
date: 2022-09-20T02:55:00+00:00
description: 最近学习了 sharding sphere 中的jdbc组件，此文作为学习记录方便以后复习。中间件官网：Apache Sharding Sphere学习准备快速开始分表创建库表：CREATE DATABASE `db_device_0` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE TABLE `tb_device_0` ( `device_id` BIGINT NOT NULL AUTO_INCREMENT, `device_type` INT DEFAULT NULL, PRIMARY KEY ( `device_id` ) ) AUTO_INCREMENT = 9 DEFAULT CHARSET = utf8mb4; CREATE TABLE `tb_device_1` ( `device_id` BIGINT NOT NULL AUTO_INCREMENT, `device_type` INT DEFAULT NULL, PRIMARY KEY ( `device_id` ) ) AUTO_INCREMENT = 10 DEFAULT CHARSET = utf8mb4;引入依赖：因为是demo项目，所以直接使用了JDK17pom.xml<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.7.1</version> </parent> <groupId>fun.psgame</groupId> <artifactId>sharding-demo</artifactId> <version>0.0.1-SNAPSHOT</version> <name>sharding-demo</name> <description>sharding-demo</description> <properties> <java.version>17</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.24</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.2</version> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-st…

Spring Data Jpa 分表处理
type: item
date: 2022-07-19T02:53:00+00:00
description: Spring Data Jpa 动态表处理Jpa 分表问题现在有一张学生表t_stu按年份进行了处理，物理表分别是t_stu_2020、t_stu_2021、t_stu_2022这样 如果是mybatis，可以直接把表后缀传入sql，然后使用t_stu_${year}对表名进行拼接 但是对于Jpa这个全自动化ORM框架来说不太好处理，因为Jpa的表名是写在实体类的注解上的，在运行时不能修改：// 实体类 @Entity @Table(name = "t_stu") // 这里的表名定义是无法在运行时修改的 @Getter @Setter public class TVisionStu { private static final long serialVersionUID = 1L; @Id @Column(name = "id") private String id; @Column(name = "name") private String name; } // dao 层 @Repository public interface TVisionStuDao extends JpaRepository<TVisionStu, String>, JpaSpecificationExecutor<TVisionStu> {}解决方案Hibernate 插件方式Hibernate 插件可以在sql执行前对sql进行修改，从而达到动态表的功能import org.apache.commons.lang3.StringUtils; import org.hibernate.EmptyInterceptor; import org.springframework.stereotype.Component; @Component public class HibernateInterceptor extends EmptyInterceptor { @Override public String onPrepareStatement(String sql) { String schoolYear = ReqContextHolder.getSchoolYear(); // 这里对表名进行替换 if (StringUtils.isNotBlank(schoolYear)) { return sql.replaceAll("t_stu", "t_stu_" + schoolYear); } return super.onPrepareStatement(sql); } }其中的ReqContextHolder是一个线程安全的类，用来保存要查找的实际年份public class ReqContextHolder { private ReqContextHolder() { } protected static ThreadLocal<String> schoolYearThreadLocal = new ThreadLocal<>(); public static void setSchoolYear(String schoolYear) { schoolYearThreadLocal.set(schoolYear); } public static String getSchoolYear() { return schoolYearThreadLocal.get(); } public static void remove() { schoolYearThreadLocal.remove(); } }然后在application.yml中对插件进行配置：spring: # 设置jpa插件，可以实现分表 jpa: properties: hibernate: ejb: # 注册自定义的插件类 interceptor: com.power.aop.HibernateInterceptor实际使用的话就像这样： @RestController @RequestMapping("/stu") public class StuController{ @GetMapping("/query") public List<TVisionStu> query(@RequestParam("schoolYear") String schoolYear) { ReqContextHolder.setSchoolYear(schoolYear); List<TVisionStu> list = this.stuDao.findAll(); ReqContextHolder.remove(); return list; } }然后就可以到sql可以正常执行了。如果觉…

Oracle 生僻字处理(mybatis、mybatis-plus、通用mapper)
type: item
date: 2022-06-07T09:02:00+00:00
description: 问题：姓名中的生僻字查询显示问号?再现环境：数据库：Oracle Database 12c后端：JDK 1.8，mybatis姓名字段使用的是VARCHAR2保存生僻字䶮，数据库可视化工具显示为?，mybatis查询出来同样显示?解决方法（已验证）修改保存姓名的字段类型为NVARCHAR2：alter table xxx表 modify NAME NVARCHAR2(500);为mybatis添加NVARCHAR2的类型处理器：NVarcharTypeHandler类package com.power.isolated.aop; import org.apache.ibatis.type.BaseTypeHandler; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.MappedJdbcTypes; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * NVARCHAR汉字生僻字乱码问题。 * 主要是 NStringTypeHandler 中，没有setNString() */ @MappedJdbcTypes(JdbcType.NVARCHAR) public class NVarcharTypeHandler extends BaseTypeHandler<String> { /** * 重点，使用 setNString 设置 * @param ps * @param i * @param parameter * @param jdbcType * @throws SQLException */ @Override public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException { ps.setNString(i, parameter); } @Override public String getNullableResult(ResultSet rs, String columnName) throws SQLException { return rs.getString(columnName); } @Override public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException { return rs.getString(columnIndex); } @Override public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { return cs.getString(columnIndex); } } 在 mybatis 配置文件mybatis-config.xml中配置类型处理器：<typeHandlers> <typeHandler handler="com.power.isolated.aop.NVarcharTypeHandler"/> </typeHandlers>注意标签的顺序，顺序不正可能导致启动出错。mybatis配置文件标签顺叙 properties-->settings-->typeAliases-->typeHandlers-->objectFactory-->objectWrapperFactory-->reflectorFactory-->plugins-->environments-->databaseIdProvider-->mappers修改mapper.xml文件中字段的类型，即 resultMap 中的 jdbcType 和参数中的 jdbcType：例如：<result column="NAME" property="isolatedName" jdbcType="NVARCHAR"/>或者select * from xxx where NAME = #{name, jdbcType=NVARCHAR}mybatis-plusmybatis-plus 除了需要上面的步骤以外，还需要修改实体类的注解：/** * 姓名 */ @TableField(value = "NAME", jdb…

Spring动态数据源
type: item
date: 2021-01-22T08:10:00+00:00
description: AbstractRoutingDataSourceDataSourceLookupTODO...

Spring中@Transactional失效问题
type: item
date: 2020-09-25T10:36:00+00:00
description: Spring中@Transactional失效Spring中的声明式注解@Transactional很大程度的方便了开发者进行DB数据保存。但是在一些特殊情况下，可能会造成注解不是按想定的方式生效，这里说几种可能造成的几种情况。常见的几种情况：异常被捕获这是一种比较简单不过稍不注意也可能会犯的情况。 Spring中事务提交还是回滚是根据调用的方法是否抛出异常来决定的，因此如果把异常捕获之后又不抛出的话，即使出了问题，事务还是会提交。@Autowired private ClassB b; @Autowired private ClassC c; @Transactional public void methodA(){ try { b.methodB(); c.methodC(); } catch (Exception e) { } }上例中，想要的结果是b.methodB()和c.methodC()同时提交或回滚，但是由于异常被捕获，即使在执行方法C的时候出现了异常，方法B的操作仍旧会生效。 （如果方法A是一次转账，方法B是转账中的加钱操作，方法C是减钱操作，B和C只执行其中一个的话会导致总金额就发生了变化）@Transactional修饰了非public方法这种也是有可能犯的一种情况。 @Transactional只能用于 public 的方法上，否则事务不会失效，如果要用在非 public 方法上，可以开启AspectJ 代理模式。（默认代理模式CGlib） CGlib是使用继承进行动态代理的，所以理论上protect方法无修饰符时应该也可以？没有测试，有空试一下。同一个类中的方法调用这是一种非常容易犯，又不容易察觉的情况。@Component public class A{ @Transactional public void methodA() { methodB(); } @Transactional public void methodB() { // do something } }上例中，methodB的事务是不生效的，因为这里是同一个类中的调用（更确切的说是同一个类同一个对象中），Spring事务的原理是调用时检查@Transactional注解，然后生成代理类进行事务管理，但是内部调用时不会生成代理类（或者说默认不会），因此也就无法进行事务管理。这种情况有好几种解决方法，下面会说到。非常见的情况非常见是指对@Transactional进行了一些属性的配置导致不生效。@Transactional 注解属性 rollbackFor 设置错误rollbackFor 可以指定能够触发事务回滚的异常类型。Spring默认抛出了未检查unchecked异常（继承自 RuntimeException 的异常）或者 Error才回滚事务；其他异常不会触发回滚事务。如果在事务中抛出其他类型的异常，但却期望 Spring 能够回滚事务，就需要指定rollbackFor属性。// 希望自定义的异常进行回滚 @Transactional(propagation= Propagation.REQUIRED,rollbackFor= MyException.class)@Transactional 注解属性 propagation 设置错误这种失效是由于配置错误，若是错误的配置以下三种 propagation，事务将不会发生回滚。PROPAGATION.SUPPORTS：如果当前存在事务，则加入该事务；如果当前没有事务，则以非事务的方式继续运行。PROPAGATION.NOT_SUPPORTED：以非事务方式运行，如果当前存在事务，则把当前事务挂起。PROPAGATION.NEVER：以非事务方式运行，如果当前存在事务，则抛出异常。propagation默认值PROPAGATION.REQUIRED：没有事务时创建事务。有事务了则加入该事务（相当于使用一个session）同一个类中事务方法调用解决方法方法拆分这是一种最简单的方法，也就是把上面例子中的methodB拆分到一个单独的类里面，这样就是一般情况下的事务调用。下面三种方法都是在methodB上添加@Transactional(propagation = Propagation.REQUIRES_NEW)之后进行的测试使用AspectJ代理具体操作就是，application.yml中需要配置spring.aop.auto ：true，然后在启动类开启AspectJ代理，并暴露代理类：@EnableAspectJAutoProxy(exposeProxy = true)这样的话就可以在调用的时候获取到代理类，并进行方法调用：((TestClassA)AopContext.cur…

MongoDB基础
type: item
date: 2020-03-07T07:05:00+00:00
description: MongoDB基础传统关系型数据库在面对在面对高并发、数据量大、高扩展和高可用时有些力不从心，MongoDB则比较容易应对以上情况。存储结构SQL术语/概念MongoDB术语/概念解释/说明databasedatabase数据库tablecollection数据库表/集合rowdocument数据记录行/文档columnfield数据字段/域indexindex索引table joins 表连接,MongoDB不支持 嵌入文档MongoDB通过嵌入式文档来替代多表连接primary keyprimary key主键,MongoDB自动将_id字段设置为主键数据类型shell即在控制台与Mongodb进行交互MongoDB中一行数据是以JSON格式进行存储的。数据类型详表数据类型描述举例字符串UTF-8字符串都可表示为字符串类型的数据{"x" : "foobar"}对象id对象id是文档的12字节的唯一 ID{"X" :ObjectId() }布尔值真或者假：true或者false{"x":true}数组值的集合或者列表可以表示成数组{"x" ： ["a", "b", "c"]}32位整数类型不可用。JavaScript仅支持64位浮点数，所以32位整数会被自动转换。shell是不支持该类型的，shell中默认会转换成64位浮点数64位整数不支持这个类型。shell会使用一个特殊的内嵌文档来显示64位整数shell是不支持该类型的，shell中默认会转换成64位浮点数64位浮点数shell中的数字就是这一种类型{"x"：3.14159，"y"：3}null表示空值或者未定义的对象{"x":null}undefined文档中也可以使用未定义类型{"x":undefined}符号shell不支持，shell会将数据库中的符号类型的数据自动转换成字符串 正则表达式文档中可以包含正则表达式，采用JavaScript的正则表达式语法{"x" ： /foobar/i}代码文档中还可以包含JavaScript代码{"x" ： function() { / …… / }}二进制数据二进制数据可以由任意字节的串组成，不过shell中无法使用 最大值/最小值BSON包括一个特殊类型，表示可能的最大值。shell中没有这个类型。 安装Windows安装从官网下载zip包 版本命名规则：x.y.zy为奇数时表示当前版本为开发版，如：1.5.2、4.1.13y为偶数时表示当前版本为稳定版，如：1.6.3、4.0.10z是修正版本号，数字越大越好解压后在目录内手动建立一个目录用于存放数据文件，如data/db；然后进入bin目录，在此处打开控制台执行：mongod --dbpath=..\data\db即可看到启动，默认端口为27017，也可以通过--port指定端口配置文件方式启动可以在data目录中创建一个config文件夹，创建一个mongod.conf文件，写入内容：storage: #The directory where the mongod instance stores its data.Default Value is "\data\db" on Windows. dbPath: ../data/db然后在bin目录控制台执行mongod -f ../data/config/mongod.conf #或者 mongod --config ../data/config/mongod.conf即可启动。Linux安装先到官网下载压缩包mongod-linux-x86_64-4.0.10.tgz。上传到Linux并解压：tar -xvf mongodb-linux-x86_64-4.0.10.tgz移动：mv mongodb-linux-x86_64-4.0.10 /usr/local/mongodb创建目录：#数据存储目录 mkdir -p /mongodb/single/data/db #日志存储目录 mkdir -p /mongodb/single/log新建并修改配置文件：vim /mongodb/single/mongod.conf，文件内容如下systemLog: #MongoDB发送所有日志输出的目标指定为文件 destination: file #mongod或mongos应向其发送所有诊断日志记录信息的日志文件的路径 path: "/mongodb/single/log/mongod.log" #当mongos或mongod实例重新启动时，mongos或mongod会将新条目附加到现有日志文件的末尾。 logAppend: true storage: #mongod实例存储其数据的目录。storage.dbPath设置仅适用于…