近期,由于客户方要求,需要将我们当前项目中用的pgsql转为MySQL,领导直接要求,将产品做成兼容MySQL、Oracle、PgSql。以下是我这边遇到的一些问题以及总结:
一、关于差异性
其实作为脚本语言,基本上都是没啥大问题的,但是还是有些小问题,以下是我总结的几点:
1、数据库json、jsonb类型。
pgsql执行这种字段类型,但是mysql和oracle需要在对应的版本里面才会支持。mysql5.7是肯定不支持这种。
2、字段长度限制。
Oracle12.2版本之前的标识符长度为varchar(32)。Oracle12.2版本新特性中标识符长度变更为128。在32的时候,有外键之类的字段,如果长度超过32位,Oracle语句执行失败。
3、关于MySQL索引。
Mysql 1071 – Specified key was too long: max key length is 767 bytes。
以utf8mb4字符集字符串类型字段为例。utf8mb4是4字节字符集,默认支持的索引字段最大长度是191字符(767字节/4字节每字符 = 191字符)。因此在varchar(255)或者char(255)类型字段上创建索引会失败。在5.6中可以在界面上操作修改。在5.7中不能修改。在5.6中默认为off,可以在界面上修改为on,在5.7中默认为on,不用修改。
4、sql结尾的分号差异。
mysql 的sql允许使用英文分号结尾。Oracle的sql不允许使用英文分号结尾。pgsql也不允许分号结尾。
5、分页查询差异。
mysql数据库的分页查询sql使用limit关键字分页。Oracle数据库的分页查询:在Oracle11g以及以下版本使用子查询的方式分页,Oracle12g及其以上版本支持使用fetch next / rows only关键字方式来分页。pgsql使用标准的limit offset关键字分页。
6、自增字段差异。
mysql数据库的自增字段在建表时,对于整型字段,可以使用auto_increment关键词配置为自增字段。mysql中一个表最多有一个自增字段。
pgsql在创建表时,如果使用serial类型,默认生成的自增序列名为:表名 + ‘’ + 字段名 + ‘seq’。pgsql中一张表支持存在多个自增字段,在使用last_insert_id() 时,需要指定序列名。可以单纯创建序列,在见表语句中自增的字段指定使用的序列名称。
Oracle同pgsql类似。
7、具体函数
例如时间转换函数。基本上都差不多。
在具体函数中,主要讲一个WITH AS () 案例。
pgsql支持with as, Oracle也支持with as。MySQL在8.0版本之前,是不支持with as语法的,而我们用的5.7的较多,所以在做兼容的时候,如果想要纯用脚本来解决, 只能用存储过程来处理这个问题。适用场景主要是在子查询,父查询之类的需要做循环查的业务场景中。
二、Springboot项目中多数据源配置
1、配置文件:
spring:
# 数据源配置
datasource:
ds1: #数据源1
driver-class-name: com.mysql.jdbc.Driver # mysql的驱动你可以配置别的关系型数据库
url: jdbc:mysql://ip:3306/db1 #数据源地址
username: root # 用户名
password: root # 密码
ds2: # 数据源2
driver-class-name: com.mysql.jdbc.Driver # mysql的驱动你可以配置别的关系型数据库
url: jdbc:mysql://ip:3307/db2#数据源地址
username: root # 用户名
password: root # 密码
当然,上面的ds1或者ds2也可以配置不同的数据源,例如Oracle,或者pgsql。
2、配置类:
@Configuration
@MapperScan(basePackages = "com.example.multipledatasource.mapper.db1", sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSourceConfig1 {
@Primary // 表示这个数据源是默认数据源, 这个注解必须要加,因为不加的话spring将分不清楚那个为主数据源(默认数据源)
@Bean("db1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.db1") //读取application.yml中的配置参数映射成为一个对象
public DataSource getDb1DataSource(){
return DataSourceBuilder.create().build();
}
@Primary
@Bean("db1SqlSessionFactory")
public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
// mapper的xml形式文件位置必须要配置,不然将报错:no statement (这种错误也可能是mapper的xml中,namespace与项目的路径不一致导致)
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/db1/*.xml"));
return bean.getObject();
}
@Primary
@Bean("db1SqlSessionTemplate")
public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
}
@Configuration
@MapperScan(basePackages = "com.example.multipledatasource.mapper.db2", sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSourceConfig2 {
@Bean("db2DataSource")
@ConfigurationProperties(prefix = "spring.datasource.db2")
public DataSource getDb1DataSource(){
return DataSourceBuilder.create().build();
}
@Bean("db2SqlSessionFactory")
public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/db2/*.xml"));
return bean.getObject();
}
@Bean("db2SqlSessionTemplate")
public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
}
关于配置数据这个,本项目代码我捞不出来。只能网上copy。具体可以查找其他资料。
三、DatabaseIdProvider
databaseId属性: 如果配置了 databaseIdProvider,MyBatis 会加载所有的不带 databaseId 或匹配当前 databaseId 的语句;如果带或者不带的语句都有,则不带的会被忽略。新增,修改和删除都有这个属性。
1、使用
<insert id="insert">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
<if test="_databaseId == 'oracle'">
select seq_users.nextval from dual
</if>
<if test="_databaseId == 'db2'">
select nextval for seq_users from sysibm.sysdummy1"
</if>
</selectKey>
insert into users values (#{id}, #{name})
</insert>
databaseId也可以直接写在insert或者select标签页内,当作一个属性值。
2、配置
@Configuration
public class DataSourceConfig {
@Value("${mybatis.mapper-locations}")
private String mapperLocations;
@Primary
@Bean(name = "dataSource")
@ConfigurationProperties("spring.datasource.hikari")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public JdbcTemplate jdbcTemplate() {
return new JdbcTemplate(dataSource());
}
@Bean
public DatabaseIdProvider databaseIdProvider() {
DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
Properties p = new Properties();
p.setProperty("Oracle", "oracle");
p.setProperty("MySQL", "mysql");
p.setProperty("PostgreSQL", "postgresql");
p.setProperty("DB2", "db2");
p.setProperty("SQL Server", "sqlserver");
databaseIdProvider.setProperties(p);
return databaseIdProvider;
}
@Primary
@Bean
public SqlSessionFactoryBean sqlSessionFactoryBean(@Qualifier("dataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dataSource);
factoryBean.setDatabaseIdProvider(databaseIdProvider());
factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
return factoryBean;
}
}
差不多了!