问题0. URL和Driver
MySQL
// URL
jdbc:mysql://127.0.0.1:3306
// Driver
com.mysql.jdbc.Driver
Postgresql
// URL
jdbc:postgresql://127.0.0.1:5432/
// Driver
org.postgresql.Driver
postgresql url最后的"/"是必须的！！！因为Postgresql的JDBC URL格式前缀为
jdbc:postgresql:，结尾有两种：
- 以"/"结尾，不指定database
- 以"database"结尾
问题1. 判断database存在与否
MySQL采用获取catalogs的方式获取database列表，即conn.getMetaData().getCatalogs()，然后判断是否存在预期的数据库
public boolean existsDatabase() {
try (Connection conn = this.openWithoutDB(0);
ResultSet result = conn.getMetaData().getCatalogs()) {
while (result.next()) {
String dbName = result.getString(1);
if (dbName.equals(this.database)) {
return true;
}
}
} catch (Exception e) {
throw new BackendException("Failed to obtain MySQL metadata, " +
"please ensure it is ok", e);
}
return false;
}
Postgresql在pg_catalog.pg_database表中查看是否存在预期的database
@Override
public boolean existsDatabase() {
String statement = String.format(
"SELECT datname FROM pg_catalog.pg_database " +
"WHERE datname = '%s';", this.database());
try (Connection conn = this.openWithoutDB(0)) {
ResultSet result = conn.createStatement().executeQuery(statement);
return result.next();
} catch (Exception e) {
throw new BackendException("Failed to obtain MySQL metadata, " +
"please ensure it is ok", e);
}
}
问题2. 创建database
MySQL创建语句
CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARSET utf8 COLLATE utf8_bin;
为保持字符串的顺序，需要设置
COLLATE utf8_bin，比如"123"<“1234”
Postgresql创建语句
CREATE DATABASE %s ENCODING='UTF-8' TEMPLATE=template0 LC_COLLATE='C' LC_CTYPE='C';
- Postgresql创建database不支持
IF NOT EXISTS- 要保持字符顺序，需要设置
LC_COLLATE和LC_CTYPE为C- 要指定
LC_COLLATE和LC_CTYPE必须设置TEMPLATE=template0
问题3. 删除database
MySQL删除database
DROP DATABASE IF EXISTS %s;
MySQL删除database可能会有超时异常，目前采取的应对方式是捕获忽略
Postgresql删除database
// 禁止后续连接
REVOKE CONNECT ON DATABASE %s FROM public;
// 仅保留当前连接（断开其他连接）
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity WHERE pg_stat_activity.datname = '%s';
// 删除database
DROP DATABASE IF EXISTS %s;
问题4. Create table, Drop table和Truncate table
create table
// mysql
CREATE TABLE IF NOT EXISTS (columnName dataType, columnName dataType, ..., PRIMARY KEY (columnName, columnName, ...)) ENGINE=InnoDB
// postgresql
CREATE TABLE IF NOT EXISTS (columnName dataType, columnName dataType, ..., PRIMARY KEY (columnName, columnName, ...))
drop table
// mysql
DROP TABLE IF EXISTS %s;
// postgresql
DROP TABLE IF EXISTS %s CASCADE;
truncate table
// mysql
TRUNCATE TABLE %s;
// postgresql
TRUNCATE TABLE %s CASCADE;
问题5. Insert语句
MySQL
REPLACE INTO table (columnName, columnName, ...) VALUES (?, ?, ...)
Postgresql
INSERT INTO table (columnName, columnName, ...) VALUES (?, ?, ...)
ON CONFLICT (primaryColumnName, primaryColumnName, ...)
DO UPDATE SET columnName = ?, columnName = ?, ...
Postgresql没有
replace into语法
问题6. 分页
MySQL和Postgresql的分页都是同种方式：
- 根据页面
limit，查询limit+1条数据 - 使用第
limit+1条数据作为pagestate - 下次查询采用相同SQL语句，后面增加
WHERE columns >= value(limit+1)
一般情况下，这都可以正常工作。
但是也存在一个特例(假设对于同一张表，如果插入顺序是(a varchar(255), b varchar(255))：(‘x’, ‘6’)， (‘x’, ‘3’)， (‘x’, ‘10’)， (‘x’, ‘1’))：
- 当g.V()或者g.E()遍历时，第一次是没有分页的，也就是SQL是
select * from table limit 3，这种时候postgresql结果是按照插入顺序返回的，不是字符顺序，比如虽然有4条记录，但是limit=3时，返回的是插入顺序('x', '6')， ('x', '3')， ('x', '10') - 而后续的查询的SQL语句是
select * from table where a = 'x' and b > 0 limit 3，此时的返回结果是按照字符顺序排序的，结果只有('x', '1')，('x', '10')，('x', '3')
为解决这个问题，对所有的postgresql查询语句，都增加order by语句，使得查询结果永远保持相同的顺序
// Set order-by to keep results order consistence for PostgreSQL result
protected String orderByKeys() {
if (this.orderByKeys != null) {
return this.orderByKeys;
}
int i = 0;
int size = this.tableDefine().keys().size();
StringBuilder select = new StringBuilder(" ORDER BY ");
for (HugeKeys hugeKey : this.tableDefine().keys()) {
String key = formatKey(hugeKey);
select.append(key).append(" ");
select.append("ASC ");
if (++i != size) {
select.append(", ");
}
}
this.orderByKeys = select.toString();
return this.orderByKeys;
}
问题7. 关闭JDBC信息
Postgresql关闭JDBC信息
new URIBuilder().addParameter("loggerLevel", "OFF")
问题8. 转义和\u0000
\u0000 在二级索引表中的field values列表示空字符串，即""
- 索引插入时，需要把
""转为\u0000存储 - 查询时，需要将
""转为\u0000构造查询语句
因为有些后端无法区别
""和null，所以引入的\u0000
MySQL
- MySQL支持
\u0000 \u0000和其他特殊字符统一进行转义
Postgresql
- 在Postgresql中，
\u0000具有特殊含义，不能作为值存储和查询 - Postgresql支持
""，所以无需\u0000，转回""进行存储和查询 - 其他特殊字符转义，采用
org.postgresql.core.Utils.escapeLiteral(builder, value, false)进行转义即可
问题9. float、double、decimal
postgresql
graph_range_indexes field_values可能是float（java）或者double（java）
采用PreparedStatement语句时，通过setObject()填充参数
- JDBC设置列为
float，pg server实际为double precision，- float测试失败
- 没发出去之前都对
- commit之后，15位decimal digit，
.后有数的随机补齐
- double测试通过
- 没发出去前，
Java的double最多为17位decimal digit(从左往右数，第一个不是0的开始统计) - 发到server端，15位decimal digit，
.后有数的:- 不足15位
随机补齐 - 超过15位，
round
- 不足15位
double的测试都能跑的过
- 没发出去前，
- float测试失败
// float commit到server之后
hugegraph=# select * from graph_range_indexes;
index_label_id | field_values | element_ids
----------------+-----------------------+-------------
165 | 7 | S1103:1 //没有".",保持原样，不补齐，不截断
165 | 3.14000010490417 | S1103:2 // 3.14是float类型，进行随机补齐
165 | 3.14159202575684 | S1103:3 // 3.141592是float类型，进行随机补齐
165 | 1234.56701660156 | S1103:4 // 1234.567是float类型，进行随机补齐
165 | 3.40282346638529e+38 | S1103:5 // 3.4028235E38是float类型，自动补齐可以round的值（神奇！！！）
165 | -3.40282346638529e+38 | S1103:6
165 | 1.40129846432482e-45 | S1103:7 // 1.4E-45是float类型，自动补齐
165 | -1.40129846432482e-45 | S1103:8
// double commit到server之后
// 超过15 digit的
hugegraph=# select * from graph_range_indexes;
index_label_id | field_values | element_ids
----------------+------------------------+-------------
10 | 0.123456789012346 | S101:0 // 0.123456789012345678901是double,Java round至17位，pg round至15位
10 | 1.79769313486232e+308 | S101:1 // 1.7976931348623157E308是double，pg round至15位
10 | -1.79769313486232e+308 | S101:2
10 | 4.94065645841247e-324 | S101:3
10 | -4.94065645841247e-324 | S101:4
//不足15 digit的
hugegraph=# select * from graph_range_indexes;
index_label_id | field_values | element_ids
----------------+------------------------+-------------
110 | 7 | S749:1 //没有".",保持原样，不补齐，不截断
110 | 3.14 | S749:2 // 3.14是double类型，不同于上面，不补齐！！！！
110 | 3.14159265358979 | S749:3 // pg round至15位
110 | 12345678901234.6 | S749:4 // pg round至15位
110 | 3.4028235e+38 | S749:5 // double不补齐，保持原样
110 | -3.4028235e+38 | S749:6 // double不补齐，保持原样
110 | 1.79769313486231e+308 | S749:7 // 1.79769313486231e+308本来就这样，无需变动
110 | -1.79769313486231e+308 | S749:8 // -1.79769313486231e+308本来就这样，无需变动
110 | 1.23456789098765e-307 | S749:9 // 1.23456789098765e-307本来就这样，无需变动
110 | -1.23456789098765e-307 | S749:10 // -1.23456789098765e-307本来就这样，无需变动
有小数点的都转为了15个
decimal digit(从左往右数，第一个不是0的开始统计)
小结：
- pg客户端设置类型为float时，server端实际为double precision
- java float 8位有效数字（4byte），double 17位有效数字（8byte），
- pg的double precision是15位有效数字
- pg的JDBC Driver并不会改变float和double的值
- pg server接收带类型的参数进行不同处理：
- float，
- 如果有小数点后的数字，补齐至15位
- 没有小数点后的数字，保持不变
- double，
- 少于15位保持不变，
- 超过15位数字，round
- float，
“有效数字”是指从左往右数第一个不是0的开始
- 设置为decimal，pg server端为numeric。
- JDBCclient发出去的内容与1中一样
- pg server接到数据的处理方式：
- float(java 8 digit)=>float(pg 6 digit)=>decimal
- double(java 17 digit)=>double(pg 15 digit)=>decimal
两者都会丢失精度
// float
index_label_id | field_values | element_ids
----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
165 | 7 | S1103:1
165 | 3.14 | S1103:2
165 | 3.14159 | S1103:3
165 | 1234.57 | S1103:4
165 | 340282000000000000000000000000000000000 | S1103:5
165 | -340282000000000000000000000000000000000 | S1103:6
165 | 0.0000000000000000000000000000000000000000000014013 | S1103:7
165 | -0.0000000000000000000000000000000000000000000014013 | S1103:8
109 | 3.14159265358979 | S742:3
109 | 12345678901234.6 | S742:4
109 | 179769313486231000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | S742:7
109 | -179769313486231000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | S742:8
109 | 0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000123456789098765 | S742:9
109 | -0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000123456789098765 | S742:10
// double 超过15位
index_label_id | field_values | element_ids
----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
10 | 0.123456789012346 | S101:0
10 | 179769313486232000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | S101:1
10 | -179769313486232000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | S101:2
10 | 0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000494065645841247 | S101:3
10 | -0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000494065645841247 | S101:4
// double 不足15位
index_label_id | field_values | element_ids
----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
109 | 7 | S742:1
109 | 3.14 | S742:2
109 | 3.14159265358979 | S742:3
109 | 12345678901234.6 | S742:4
109 | 340282350000000000000000000000000000000 | S742:5
109 | -340282350000000000000000000000000000000 | S742:6
109 | 179769313486231000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | S742:7
109 | -179769313486231000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | S742:8
109 | 0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000123456789098765 | S742:9
109 | -0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000123456789098765 | S742:10
- 设置为Decimal，但是把数字封装为java BigDecimal之后发出，pg server实际为numeric
- JDBC以BigDecimal发出参数，完全保留所有信息
- pg server把BigDecimal转为pg的decimal，不丢失任何精度信息
pg的decimal对应java的BigDecimal
// float
index_label_id | field_values | element_ids
----------------+---------------------------------------------------+-------------
166 | 7.0 | S1110:1
166 | 3.14 | S1110:2
166 | 3.141592 | S1110:3
166 | 1234.567 | S1110:4
166 | 340282350000000000000000000000000000000 | S1110:5
166 | -340282350000000000000000000000000000000 | S1110:6
166 | 0.0000000000000000000000000000000000000000000014 | S1110:7
166 | -0.0000000000000000000000000000000000000000000014 | S1110:8
// double 超过15 digit
index_label_id | field_values | element_ids
----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
10 | 0.12345678901234568 | S101:0
10 | 179769313486231570000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | S101:1
10 | -179769313486231570000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | S101:2
10 | 0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000049 | S101:3
10 | -0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000049 | S101:4
// double 不足15 digit
index_label_id | field_values | element_ids
----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
110 | 7.0 | S749:1
110 | 3.14 | S749:2
110 | 3.141592653589793 | S749:3
110 | 12345678901234.566 | S749:4
110 | 340282350000000000000000000000000000000 | S749:5
110 | -340282350000000000000000000000000000000 | S749:6
110 | 179769313486231500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | S749:7
110 | -179769313486231500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | S749:8
110 | 0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001234567890987654 | S749:9
110 | -0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001234567890987654 | S749:10
总结
解决方案
pg server采用decimal/numeric，client程序在对preparedStatement使用setObject()填充参数时，将数字转化为BigDecimal填入
2403
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
