人大金仓数据库与mysql比较

简介

人大金仓数据库是基于 PostgreSQL 开发的。

SQL语言

语法

关键字

KES：

MYSQL：

语句

运算符

KES参考：http://help.kingbase.com.cn/v8/development/sql-plsql/plsql/plsql-plsql-language-fundamentals.html?highlight=%E8%BF%90%E7%AE%97%E7%AC%A6

MYSQL参考：https://dev.mysql.com/doc/refman/8.0/en/non-typed-operators.html

数据类型

表格里的/表示不存在对应的特性。

在人大金仓中 空字符会自动转换为NULL。与空字符串比较，其实就是与NULL比较，不能用等号。#kingbase.config可以设置 ora_input_emptystr_isnull = false，限制这种转换。

参考：https://help.kingbase.com.cn/v8/development/sql-plsql/sql/datatype.html

注意点

1、KES中的整型不存在有符号、无符号的分别。都是有符号数。

2、KES中的自增使用的不是Auto_Increatment，是XXXXserial类型。(好像可以兼容)

3、Mysql中字符串类型中n指的是字符，但是总长度受字节限制。KES中可以通过系统设置是字符，还是字节。

4、一些特殊类型

• UUID： 是128位的存储，不是字符串。
• IP地址：
• 对象标识符类型
• 伪类型

5、KES有一些类型叫范围类型，组合类型，对象类型，等等，都不建议使用，太生僻了。

6、很多产品相关的类型，建议完全不用。

这些KES的方言类型完全不建议使用。另外有些违背了数据库范式。

数据类型比较

跟Mysql类似

数据类型转换

支持将值从一种数据类型隐式和显式转换为另一种数据类型。

建议都采用显示转换。

DML

仅标准SQL是一致的，其他不完全一致。

DDL

注意：

• 仅列常用语法，或者某个语法的常用部分。
• 仅列由于mysql语法引出的，不列KES的方言特性。

KES参考：https://help.kingbase.com.cn/v8/development/sql-plsql/sql/SQL_Statements_6.html

创建表

-- KESCREATE TABLE [ IF NOTEXISTS ] table_name ( [{ column_name data_type [ INVISIBLE | VISIBLE ] [ COLLATE collation ] [ column_constraint [ ... ] ]| table_constraint| LIKE source_table [ like_option ... ] }[, ... ]] )[ INHERITS ( parent_table [, ... ] ) ][ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [COLLATE collation ] [ opclass ] [, ... ] ) [ partition_extented_spec ] ][ USING method ][ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ][ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ][ WITH ( ONCOMMIT = value ) ][ TABLESPACE tablespace_name ][ { ENABLE | DISABLE } [ VALIDATE | NOVALIDATE ] constraint [ ... ] ][ ENCRYPTED [BY tablekey ]]

自增列

KES:

-- 通过 serial，smallserial，bigserial 。可以为NULL。CREATE TABLE cinemas (id serial,name text,location text) -- 通过auto_increment ，会自动转换为 serial。-- pgsql 不支持，人大金仓支持。CREATE TABLE cinemas (id int auto_increment,name text,location text) 

MYSQL：

CREATE TABLE cinemas (id int auto_increment,name text,location text) 

主键

KES：

-- 列约束 CREATE TABLE distributors (did integer CONSTRAINT firstkey PRIMARY KEY,namevarchar(40));CREATE TABLE distributors (did integer PRIMARY KEY,namevarchar(40));-- 表约束CREATE TABLE distributors (did integer,namevarchar(40),PRIMARY KEY(did));

MYSQL:

CREATE TABLE distributors (did integer PRIMARY KEY,namevarchar(40));-- 表约束CREATE TABLE distributors (did integer,namevarchar(40),PRIMARY KEY(did));

唯一索引

KES:

-- 字段约束CREATE TABLE distributors (did integer,namevarchar(40) UNIQUE);-- 或者 表约束CREATE TABLE distributors (did integer,namevarchar(40),UNIQUE(name));

MYSQL:

-- 字段约束CREATE TABLE distributors (did integer,namevarchar(40) UNIQUE);-- 或者 表约束CREATE TABLE distributors (did integer,namevarchar(40),UNIQUE(name));

普通索引

通过约束可以创建索引

注释

MYSQL：

-- 加在表定义以及列定义后面comment 'this is a comment'-- 没有专门命令删除comment。

KES：

-- 表加注释Comment ontable [tablename] IS'表注释'-- 列加注释Comment oncolumn[tablename].ColumnNameIS'列注释'-- 删除表注释Comment ontable [tablename] ISNULL-- 删除列注释Comment oncolumn[tablename].ColumnNameISNULL

自动更新修改时间

MYSQL：

`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,`gmt_update` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

KES：

 modtime timestamp DEFAULT current_timestamp-- 插入时的默认值

KES没有 ON UPDATE CURRENT_TIMESTAMP，只能通过创建触发器来实现了。

分区

不一致。略。

通过一个查询创建表

KES：

CREATE TABLE films_recent ASSELECT * FROM films WHERE date_prod >= '2002-01-01';

MYSQL：

CREATE TABLE films_recent ASSELECT * FROM films WHERE date_prod >= '2002-01-01';

索引

创建索引

KES参考：http://help.kingbase.com.cn/v8/admin/general/administrator-guide/14-managing-indexes.html?highlight=%E7%B4%A2%E5%BC%95

KES：

-- 显式地创建索引CREATE INDEX emp_ename_index ON emp(ename) TABLESPACE users_space;--创建唯一索引CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE users_space;-- 创建B+树索引 (索引可以指定 排序，NULL顺序处理)CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);-- 创建HASH索引 CREATE INDEX name ON table USING HASH (column);-- 函数索引CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));-- 多列索引CREATE INDEX test2_mm_idx ON test2 (major, minor);

注意：本地和全局索引

MYSQL:

mysql的索引类型只有BTree和Hash

修改索引

略

删除索引

KES：

DROP INDEX [index_name];-- 删除通过约束创建的索引ALTER TABLE [tablename] DROP CONSTRAINT [constraint_name];

MYSQL:

DROP INDEX index_name ON tbl_name

Mysql的索引是在表上的，不同table上的索引名称可以相同。

修改表

-- KESALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]action [, ... ]

ONLY 表示只修改本表，不修改后代表。 默认都必须加上。

另外不建议使用后代表。

新增列

KES：

ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]... ...

MYSQL：

ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]DROP [COLUMN] col_nameALTER [COLUMN] col_name .......MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]RENAME COLUMN old_col_name TO new_col_name

MYSQL修改列方式更复杂。

修改列类型

KES：

ALTER [ COLUMN ] column_name ...MODIFY column_name [ datatype ][ NULL | NOT NULL ]MODIFY ( column_name [ datatype ][ NULL | NOT NULL ][, ... ] )

删除列

KES：

DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]

MYSQL:

DROP [COLUMN] col_name

删除表

兼容。略。

创建序列

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ INCREMENT [ BY ] increment ][ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ][ START [ WITH ] start ] [ CACHE cache ] [ NOCACHE ] [ [ NO ] CYCLE ] [ NOCYCLE ][ ORDER ] [ NOORDER ] [ OWNED BY { table_name.column_name | NONE } ]

-- 创建一个序列 (一般对应mysql 的auto_increatment)CREATE SEQUENCE serial_A START 1;-- 使用一个序列select nextval('serial_A')

序列是基于bigint的。

setval(，)用于设置当前序列号。下个序列为 设置的值加1。

删除序列

KES：

DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

MYSQL:

不涉及

函数

此处仅比较常用函数，特殊数据类型比如：xml，json，空间数据等处理函数肯定是不一样的，参考官方文档。

控制流函数

函数一致。

MYSQL参考：https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html

KES参考：http://help.kingbase.com.cn/v8/development/sql-plsql/sql/Function.html#if

数学函数

MYSQL参考：https://dev.mysql.com/doc/refman/8.0/en/numeric-functions.html

KES参考：http://help.kingbase.com.cn/v8/development/sql-plsql/sql/Function.html#id5

日期和时间函数

MYSQL参考：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

KES中的Date 是包含 日期和时间的。

MYSQL与KES中的时间部分的名字是不一样的。

SYSDATE()与NOW()区别

SYSDATE()与NOW()返回值一样。不同之处在于在同一个statement中的处理。NOW() 在同一个查询中，返回同一个值。SYSDATE()返回每次调用的值。

select a, now(),sleep(1),now()from (select '1' as a union all select '2' as a) u -- NOW()值一样。12022-09-07 16:29:1302022-09-07 16:29:1322022-09-07 16:29:1302022-09-07 16:29:13select a, sysdate(),sleep(1),sysdate()from (select '1' as a union all select '2' as a) u -- sysdate()值不一样。12022-09-07 16:30:1802022-09-07 16:30:1922022-09-07 16:30:1902022-09-07 16:30:20

字符串函数

Mysql参考： https://dev.mysql.com/doc/refman/8.0/en/string-functions.html ， https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-instr

KES参考：http://help.kingbase.com.cn/v8/development/sql-plsql/sql/Function.html#id6

说明

• SOUNDEX

  SOUNDEX()函数用于返回字符串的语音表示形式

• WEIGHT_STRING

  WEIGHT_STRING()函数来查找字符串的权重。字符。该函数以二进制字符串形式返回表示重量的值。因此，我们需要使用 HEX(WEIGHT_STRING(str))函数以可打印的形式显示重量。

  权重主要用于比较字符。

  SELECT HEX(WEIGHT_STRING('AaBbCc' ) ) AS Weight;-- OUTPUT：004100410042004200430043SELECT HEX(WEIGHT_STRING(BINARY 'AaBbCc')) AS Weight;-- OUTPUT：416142624363

KES 的 SUBSTRING的实际执行结果与官网的示例结果是不一样的。

-- 官网示例SELECT SUBSTRING('ABCDEFG',-5,4) "Substring" FROM DUAL;+-----------+| Substring |+===========+| CDEF|+-----------+--实际结果 。应该是从左边偏移SELECTSUBSTRING('ABCDEFG',-3,4),SUBSTRING('ABCDEFG',-2,4),SUBSTRING('ABCDEFG',-1,4),SUBSTRING('ABCDEFG',0,4),SUBSTRING('ABCDEFG',1,4)'','A''AB''ABC''ABCD'

类型转换

CAST

MYSQL：

CAST(expr AS type [ARRAY])-- MYSQL8.0.22支持。CAST(timestamp_value AT TIME ZONE timezone_specifier AS DATETIME[(precision)])

KES：

CAST(expr AS type [ARRAY])

Convert

MYSQL:

CONVERT(expr USING transcoding_name)CONVERT(expr,type)

CONVERT(expr USING transcoding_name) 用于把数据转换为不同的字符集。

SELECT CONVERT('abc' USING utf8mb4);-- OUTPUTabcSELECT CONVERT(_latin1'Müller' USING utf8mb4);-- OUTPUT：Müller

KES：

CONVERT(expr,type)

数据类型的转换

CAST(expr AS type [ARRAY]) 等同于 CONVERT(expr,type)

把一个表达式转化为另一种数据类型。

type支持的类型：

• BINARY[(N)]
• CHAR[(N)] [charset_info]
• DATE
• DATETIME[(M)]
• DECIMAL[(M[,D])]
• DOUBLE
• FLOAT[§]
• JSON (mysql 8.0)
• NCHAR[(N)]
• REAL
• SIGNED [INTEGER]
• spatial_type(MySQL 8.0.24)
• TIME[(M)]
• UNSIGNED [INTEGER]
• YEAR(MySQL 8.0.22)
• AT TIME ZONE(MySQL 8.0.22)

位操作函数

参考：https://dev.mysql.com/doc/refman/8.0/en/bit-functions.html

SELECT 127 | 128, 128 << 2, BIT_COUNT(15);-- OUTPUT:2555124 SELECT X'40' | X'01', b'11110001' & b'01001111'; -- OUTPUT: 6565 SELECT _binary X'40' | X'01', b'11110001' & _binary b'01001111'; -- OUTPUT:10 

在mysql 8.0之前，位操作符 仅支持 bigint，返回结果也是bigint。在mysql 8.0 之后，操作数支持二进制字符串((BINARY, VARBINARY, 和 BLOB )，返回值类型与操作符类型一致。

BIT_AND(), BIT_OR(), 和BIT_XOR() 是聚合函数，用于聚合多行记录的某列的值。

其他

Spring 集成

把官方的jar包上传到maven库

引入maven库

JDBC配置

代码

示例

数据库

Create table Test_All(id a tinyint NOT null default 0 ,b smallint NOT null default 0 ,c int NOT NULL default 0,d bigint NOT NULL default 0 ,e float(3)NOT NULL default 0,f double PRECISIONNOT NULL default 0,g date default current_timestamp ,h time ,i timestamp,j date withtime zone,k time withtime zone,l timestamp withtime zone,m char(8 byte) NOT NULL default '',n varchar(32 byte) NOT NULL default '',o bit(8) ,p boolean NOT NULL default false ,q MONEY NOT NULL default 0,u UUID NOT NULL default 0,v UUID NOT NULL default {00000000-0000-0000-0000-000000000000} ,)

Java bean

同时兼容mysql和KES，并支持切换

mybatis-plus兼容

boolean型属性

经验证，int 类型的1/0 会自动转换为boolean对应的true/false

update_date类型属性

附录

注意

官网上的文档有很多错误，一定要自己确认。

官网上的文档有很多错误，一定要自己确认。

官网上的文档有很多错误，一定要自己确认。

navicat连接KES

未找到相关资料

DBeaver连接KES

1、下载DBeaver：https://dbeaver.io/download/ 。下载windows版本。

2、安装

3、下载KES的驱动：

吐槽：人大金仓官网居然没有。

在安装目录下的 /KESRealPro/V008R006C006B0013/Interface/jdbc 下有。

4、在DBeaver中创建连接

• 创建驱动

驱动信息：

com.kingbase8.Driverjdbc:kingbase8://{host}[:{port}]/[{database}]

命令行

在安装根目录的 ./KESRealPro/V008R006C006B0013/ClientTools/bin 下有ksql 文件。需要加入到PATH中才可以直接访问。

Usage:ksql [OPTION]... [DBNAME [USERNAME]]General options:-c, --command=COMMANDrun only single command (SQL or internal) and exit-d, --dbname=DBNAMEdatabase name to connect to (default: "root")-f, --file=FILENAMEexecute commands from file, then exit-l, --list list available databases, then exit-v, --set=, --variable=NAME=VALUE set ksql variable NAME to VALUE (e.g., -v ON_ERROR_STOP=1)-V, --versionoutput version information, then exit-X, --no-ksqlrcdo not read startup file (~/.ksqlrc)-1 ("one"), --single-transaction execute as a single transaction (if non-interactive)-" />[=options] show this help, then exit--help=commandslist backslash commands, then exit--help=variables list special variables, then exitInput and output options:-a, --echo-all echo all input from script-b, --echo-errorsecho failed commands-e, --echo-queries echo commands sent to server-E, --echo-hiddendisplay queries that internal commands generate-L, --log-file=FILENAMEsend session log to file-n, --no-readlinedisable enhanced command line editing (readline)-o, --output=FILENAMEsend query results to file (or |pipe)-q, --quietrun quietly (no messages, only query output)-s, --single-stepsingle-step mode (confirm each query)-S, --single-linesingle-line mode (end of line terminates SQL command)Output format options:-A, --no-align unaligned table output mode--csvCSV (Comma-Separated Values) table output mode-F, --field-separator=STRING field separator for unaligned output (default: "|")-H, --html HTML table output mode-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)-R, --record-separator=STRING record separator for unaligned output (default: newline)-t, --tuples-onlyprint rows only-T, --table-attr=TEXTset HTML table tag attributes (e.g., width, border)-x, --expanded turn on expanded table output-z, --field-separator-zero set field separator for unaligned output to zero byte-0, --record-separator-zero set record separator for unaligned output to zero byteConnection options:-h, --host=HOSTNAMEdatabase server host or socket directory (default: "local socket")-p, --port=PORTdatabase server port (default: "54321")-U, --username=USERNAMEdatabase user name (default: "root")-w, --no-passwordnever prompt for password-W, --password force password prompt (should happen automatically)

参考：http://help.kingbase.com.cn/v8/admin/reference/ref-ksql/ksql-3.html?highlight=ksql

示例

#登录数据库(必须要指定连接数据库)./ksql -h localhost -p 54321 -U system -Wtest#带有密码的，必须不能带-h 参数。 并且 数据库的参数必须在 -w 之前。./ksqltest -p 54321 -U system -w 123456 ./ksql -p 54321 -U systemtest-w 123456 #执行脚本命令./ksqltest -p 54321 -U system -w 123456 -c 'select 1;'

数据库结构迁移

问题

mysql中的表名和字段都是小写的，迁移后发现数据都已经迁移过来。但是点击权限的时候，提示关系‘表名’不存在。

使用kingbase自带的数据库迁移工具进行迁移，迁移时需要注意，如果mysql数据库中的表和字段是小写的话需要，在迁移的时候，点击配置选择表和字段大写，才能正常迁移，不然迁移后提示关系‘表名’不存在。

疑问

ksql 登录时为什么带 -h 就不能在参数中带上密码。那怎么连接其他机器上的数据库

mysql表的DDL语句能否自动转化为KES的DDL语句。

能够设置 类似mysql ON UPDATE CURRENT_TIMESTAMP，修改列时自动更新字段的值？