数据库学习之多种数据库横向对比
前言
-
场景描述
获取表结构(字段+索引)列表信息,需要适配多种类型数据库,其中主要包括MySQL、PostgreSQL、Oracle、SqlServer、KingBase、达梦和oscar等,下面针对这些数据库做简要对比。
横向对比
-
总览说明
区别分析
MySQL
-
schema
在MySQL中创建一个Schema和创建一个Database的效果类似。
PostgreSQL
-
tablespace
PostgreSQL的tablespace代表一个操作系统目录(directory),在创建SQL对象时,可以为其指定tablespace,表示将该对象存放在tablespace对应的目录下的文件中。
-
schema
PostgreSQL中模式的概念与MySQL中Database的概念是等同的。在MYSQL中可以同时访问多个Database对象,但在PostgreSQL中只能访问一个Database对象,但在PostgreSQL中可以访问多个Schema。
-
获取方式
pg 能够根据ip和端口获取所有的database,然后根据database,获取下面所有的schema信息。
-
其他
表空间是一个存储区域,在一个表空间中可以存储多个数据库,尽管PostgreSQL不建议这么做,但我们这么做完全可行。一个数据库并不直接存储表结构等对象的,而是在数据库中逻辑创建了至少一个模式,在模式中创建了表等对象,将不同的模式指派该不同的角色,可以实现权限分离,又可以通过授权,实现模式间对象的共享,并且还有一个特点就是:public模式可以存储大家都需要访问的对象。
既然一个表在创建的时候可以指定表空间,那么就可以给一个表指定它所在的数据库表空间之外的表空间。但这样做违背了表属于模式,而模式属于数据库,数据库最终存在于指定表空间这个网的模型。
部分情况需要这样做:将表的数据存在一个较慢的磁盘上的表空间,而将表的索引存在于一个快速的磁盘上的表空间。但我们再查看表所属的模式还是没变的,它依然属于指定的模式,所以这并不违反常理。
Oracle
-
schema
在Oracle数据库中不能新建一个schema,要想创建一个schema,只能通过创建一个用户的方法解决,在创建一个用户的同时为这个用户创建一个与用户名同名的schem并作为该用户的缺省shcema。即schema的个数同user的个数相同,而且schema名字同user名字一一对应并且相同。
-
获取方式
oracle默认只有一个库(通过其他的高级组件支持多个库,可以不考虑), 创建用户就等同于创建schema,但是一个用户可以授权访问其他的schema。
-
注意事项
oracle的访问必须通过sid或者tname或者serviceName,这个必须通过配置来完成。不用考虑所有database,但是为了方便支持所有的用户或者说是schema,还是需要支持schema的模式。
SqlServer
-
获取方式
sqlServer是最灵活的,一个实例可以有多个database,一个database可以有多个schema。
其他
-
集成数据库
tdata、tdsql、tbase和tdata是个数据库一体机,类似于以前oracle配套买硬件的那种东西,并不是数据库技术。支持依赖于底层的数据库。
tdsql和tbase是分布式数据库。
-
国产数据库
国产数据库都是从pg和oracle、mysql衍生出来的,以kingbase为例,与pg类似
技术分析
获取数据库(database)
-
手工配置所有数据库
-
配置主机信息程序遍历所有数据库
注意事项
- Oracle需要知道sid或tname或servicename才能下一步操作
格式一: Oracle JDBC Thin using an SID: jdbc:oracle:thin:@host:port:SID Example: jdbc:oracle:thin:@localhost:1521:orcl 格式二: Oracle JDBC Thin using a ServiceName: jdbc:oracle:thin:@//host:port/service_name Example:jdbc:oracle:thin:@//localhost:1521/orcl.city.com 格式三:Oracle JDBC Thin using a TNSName: jdbc:oracle:thin:@TNSName Example: jdbc:oracle:thin:@TNS_ALIAS_NAME
获取约束(schema)
-
注意事项
针对多用户,多schema的,建议给一个赋予所有的schema查询权限的用户,然后交给程序去遍历。
获取表(table)
-
MySQL系列
采用表名方式:database.tableName
-
Oracle/PostgreSQL系列
采用表名方式:schema.tableName
-
SqlServer系列
采用表名方式:database.schema.tablename
常见问题
kingbase获取连接失败
-
问题描述
相同的库,包括版本都一样,但是编译环境不同,一个库可以建立连接,一个连接失败
-
部分报错信息
com.kingbase8.util.KSQLException: 致命错误: remaining connection slots are reserved for superuser manager connections (kbjdbc: autodetected server-encoding to be EUC_CN, if the message is not readable, please check database logs and/or host, port, dbname, user, password, sys_hba.conf)
-
可能原因
- 库大小写敏感配置不同;
- 连接数配置过小
-
解决方案
-- 查看大小写敏感配置 SHOW case_sensitive; -- 查看当前用户连接数 select connections(); -- 修改用户new_user的最大并发连接数为50 alter user new_user with connection limit 50; -- 修改用户new_user的最大连接时间为2小时 alter user new_user with connection duration 120; -- 修改用户new_user的最大空闲时间为2小时 alter user new_user with connect idle time 120; -- 查看系统配置 SELECT sourcefile,name,setting,applied FROM sys_file_settings; -- 等同于修改kingbase.auto.conf里MAX_CONNECTIONS配置大小 alter system set max_connections = 1000;
注意:kingbase.auto.conf的优先级高于kingbase.conf
相关概念
表空间(tableSpace)
-
概念
表空间是实际的数据存储的地方。一个数据库schema可能存在于多个表空间,相似地,一个表空间也可以为多个schema服务。
-
作用
通过使用表空间,管理员可以控制磁盘的布局。表空间的最常用的作用是优化性能,例如,一个最常用的索引可以建立在非常快的硬盘上,而不太常用的表可以建立在便宜的硬盘上,比如用来存储用于进行归档文件的表。