kingbase数据库使用遇到的问题以及解决办法【随时更新】

kingbase数据库与其他数据库的兼容问题

linux_lsh

12950人浏览 · 2022-07-28 14:41:48

linux_lsh · 2022-07-28 14:41:48 发布

觉得不错点赞收藏下吧随时更新哦有问题可以交流

1. 数据库的启停

到 Server/bin 目录下执行
进入数据库：ksql -U用户名 -W -d数据库
1. 起库：sys_ctl -D /home/kingbase/data start
停库：sys_ctl -D /home/kingbase/data stop

2.初始化数据库实例：

initdb -USYSTEM -x  密码 -D /abc/data    (大小写敏感)
initdb -USYSTEM -x  密码 -D /abc/data --enable-ci      V008R006 C005 之后的版本适用 (大小写不敏感   on是不敏感)
initdb -USYSTEM -x  密码  -D /abc/data --case-insensitive   V008R006 C004 之前适用   (大小写不敏感    on敏感  )

3.更改 search_path 解决查表不加模式名时报错：关系 xxx 不存在

ALTER DATABASE 数据库名 SET search_path to "$user", 模式名, public, sys_catalog, sys, pg_catalog;
然后执行 select sys_reload_conf(); 重载配置文件。

4.设置返回字段大写

ALTER DATABASE 数据库名 SET enable_upper_colname=on;
然后执行 select sys_reload_conf(); 重载配置文件。

5.兼容MySQL的 GROUP BY 子句中缺省字段名的语法

ALTER DATABASE 数据库名 SET sql_mode='';
然后执行 select sys_reload_conf(); 重载配置文件。

6.忘记或没有设置 SYSTEM 密码导致无法登录数据库

先去 data 目录下找到 sys_hba.conf 打开后找到最下面 IPv4 local connections: 项，
到第一行，把 scram-sha-256 改为 trust 然后保存，重启数据库后免密登录数据库，
再执行下面的语句修改system 密码
ALTER USER system PASSWORD ' 新密码 ';

7.kingbase7去除死锁的语句是什么？已经查到pid

SELECT sys_cancel_backend(pid);

8.windows跟换授权后显示write license control file faild

修改目录权限

9.windows中54321端口被占用

netstat -ano |findstr '54321'
tasklist | findstr "进程号"
taskkill /pid 进程号

10.查询表准确膨胀率的sql

select relname,n_live_tup,n_dead_tup from sys_stat_user_tables order by n_dead_tup desc;

11.输入一个日期，让sql往后或者往前推60年

select date'2022-3-5' + interval '1 year';

配置 ’ ’ 代替 null

ALTER DATABASE 数据库 set ora_input_emptystr_isnull=true;
select sys_reload_conf();

13.应用代码中事务自动提交的问题

conn.setAutoCommit(false);

14.查询模式下的所有的记录数

SELECT count(table_name) FROM information_schema.TABLES WHERE table_schema =‘information_schema’;  --schema填自己的实际的schema_name
或者：    
SELECT relname, reltuples
FROM sys_catalog.sys_class r JOIN sys_catalog.sys_namespace n
ON (relnamespace = n.oid)
WHERE relkind = 'r' AND n.nspname = 'public'
order by reltuples desc

15.模糊查询字段

SELECT * FROM TABLE_NAME WHERE CONCAT(FIELD1,FIELD2) LIKE '%STRING%'

16.日期采用date，不要后缀00:00:00

ALTER DATABASE TEST set ORA_STYLE_NLS_DATE_FORMAT to 'on';
select sys_reload_conf();

17.查看事务隔离级别

SHOW TRANSACTION ISOLATION KINGBASE_INTERNAL_LEVEL;

18.R6非交互式的方式登录数据库

ksql "host=1.1.1.1 port=54321 user=system dbname=test password=123456"

19.非交互式还原数据库

 PGPASSWORD=123456 sys_restore -h127.0.0.1   -Usystem -d abc xxx.dmp

20.数据库迁移编码报错问题

配置文件添加参数，重启数据库后生效 ignore_char_null_check=on

21.授权序列

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public to www;

22.查看当前账号会话数


select count(*), usename from sys_stat_activity group by usename;

23.设置varchar和char之间匹配导致索引失效的问题

set ora_enable_varchar_match_bpchar to on

24.查看表的存储路径

select sys_relation_filepath('表名');

25.查看会话进程

select datname,pid,usename,state,query  from sys_stat_activity;

26.通过copy还原、导出csv格式的数据

\copy table_1 from '/home/kingbase/b.csv'  csv    通过copy还原csv格式的数据到表中
\copy table_1  to  '/home/kingbase/b.csv' csv       导出表数据到csv文件

27.查看历史执行的sql命令

修改配置文件kingbase.conf,
开启参数 log_statement='all'，sys_stat_statements.track='all' ，
重启数据库。
进入数据库执行select query,calls  from sys_stat_statements

28.查看sql的执行时间

select query,calls,round(mean_exec_time,2)as mean_exec_time from 
sys_stat_statements order by mean_exec_time desc;

29.两表关联更新字段

update A set A.1 = B.1 from B where A.id = B.id

30.授权A用户访问B用户的表

grant usage on schema 模式名 to 用户名;   grant select on table 表名 to 用户名

31.时间戳转换为时间

select to_char(to_timestamp(1628088734), 'yyyy-mm-dd hh24:mi:ss')

32.设置时间分区

set ora_func_style = false;
create table part_test(id int, info text, crt_time timestamp not null);
select create_range_partitions(
'PART_TEST'::regclass,
                     'crt_time',               
                     '2018-10-01 00:00:00'::timestamp,  
                     interval '1 month',                                    
24,   
                     false) ;

33.生成UUID的命令

select sys_guid();

34.将日期格式化成特定格式

DATE_FORMAT(date TIMESTAMP,format TEXT);

35.初始化编码问题

初始化添加参数   --locate=C

36.将一个字段中的值拼接

string_agg(字段A,'分隔符')

37.将字符串转换为数字类型后面99999表示保留的位数，数字只能用9

to_number('12345','99999')

38.实现mysql的date_add功能

select current_date + interval '0 hours';

获取月份的函数

select date_part('month',now());

查看所有的序列属性

SELECT * FROM "pg_class" "c" WHERE "c"."relkind" = 'S';

查看字段名称，类型，注释

SELECT 
    a.attname as 字段名,
    format_type(a.atttypid,a.atttypmod) as 类型, 
    a.attnotnull as 非空, col_description(a.attrelid,a.attnum) as 注释   
FROM 
    pg_class as c,pg_attribute as a 
where 
    a.attrelid = c.oid 
    and 
    a.attnum>0 
    and 
    c.relname = '你的表名';

设置中文占多少字节

配置文件添加这个参数  char_default_type=''char'' 
V8R6默认就是char不需要设置

43.查看字段类型

select sys_typeof(  name )  from aa

44.查看库表数据量【不是太准】

select pg_size_pretty(pg_database_size('库名'));

查询所有的函数

SELECT
  pg_proc.proname AS "函数名称",
  pg_type.typname AS "返回值数据类型",
  pg_proc.pronargs AS "参数个数"
FROM
  pg_proc
JOIN pg_type ON (pg_proc.prorettype = pg_type.oid)

查看所有的函数，类型，拥有者（R6）

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
 CASE p.prokind
  WHEN 'a' THEN 'agg'‘’
  WHEN 'w' THEN 'window'
  WHEN 'p' THEN 'proc'
  ELSE 'func'
 END as "Type",
 pg_catalog.pg_get_userbyid(p.proowner) as "Owner"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
WHERE pg_catalog.pg_function_is_visible(p.oid)
           AND n.nspname <> 'pg_catalog'
                AND n.nspname <> 'information_schema'
                AND n.nspname <> 'sys'
                AND n.nspname <> 'sys_catalog'
ORDER BY 1, 2, 4;

用sql命令查看ddl语句

create extension dbms_metadata
SELECT dbms_metadata.get_ddl('table', 'aa');

用sql命令查看ddl语句（2）

CREATE OR REPLACE FUNCTION tabledef(text,text) RETURNS text
LANGUAGE sql STRICT AS 
$$
WITH attrdef AS (
        SELECT n.nspname, c.relname, c.oid, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ') as relopts,
        c.relpersistence, a.attnum, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as atttype, 
        (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as attdefault,
        a.attnotnull, (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) as attcollation,
        a.attidentity, a.attgenerated 
        FROM pg_catalog.pg_attribute a 
        JOIN pg_catalog.pg_class c ON a.attrelid = c.oid 
        JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 
        LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) 
        WHERE n.nspname = $1 AND c.relname = $2 AND a.attnum > 0 AND NOT a.attisdropped 
        ORDER BY a.attnum
    ), coldef AS (
        SELECT attrdef.nspname, attrdef.relname, attrdef.oid, attrdef.relopts, attrdef.relpersistence, pg_catalog.format('%I %s%s%s%s%s', attrdef.attname, attrdef.atttype, 
        case when attrdef.attcollation is null then '' else pg_catalog.format(' COLLATE %I', attrdef.attcollation) end, 
        case when attrdef.attnotnull then ' NOT NULL' else '' end, 
        case when attrdef.attdefault is null then '' else case when attrdef.attgenerated = 's' then pg_catalog.format(' GENERATED ALWAYS AS (%s) STORED', attrdef.attdefault) when attrdef.attgenerated <> '' then ' GENERATED AS NOT_IMPLEMENTED' else pg_catalog.format(' DEFAULT %s', attrdef.attdefault)  end  end,           
        case when attrdef.attidentity<>'' then pg_catalog.format(' GENERATED %s AS IDENTITY', case attrdef.attidentity when 'd' then 'BY DEFAULT' when 'a' then 'ALWAYS' else 'NOT_IMPLEMENTED' end)       else '' end ) as col_create_sql    
        FROM attrdef
        ORDER BY attrdef.attnum
    ), tabdef AS (
         SELECT coldef.nspname, coldef.relname, coldef.oid, coldef.relopts, coldef.relpersistence, concat(string_agg(coldef.col_create_sql, E',\n    ') , (select concat(E',\n    ',pg_get_constraintdef(oid)) from pg_constraint where contype='p' and conrelid = coldef.oid))  as cols_create_sql
         FROM coldef 
         GROUP BY coldef.nspname, coldef.relname, coldef.oid, coldef.relopts, coldef.relpersistence
    )
SELECT FORMAT( 'CREATE%s TABLE %I.%I%s%s%s;', 
                case tabdef.relpersistence when 't' then ' TEMP' when 'u' then ' UNLOGGED' else '' end, 
                tabdef.nspname, 
                tabdef.relname, 
                coalesce( (
                        SELECT FORMAT( E'\n    PARTITION OF %I.%I %s\n', pn.nspname, pc.relname, pg_get_expr(c.relpartbound, c.oid) ) 
                        FROM pg_class c 
                        JOIN pg_inherits i ON c.oid = i.inhrelid
                        JOIN pg_class pc ON pc.oid = i.inhparent
                        JOIN pg_namespace pn ON pn.oid = pc.relnamespace
                        WHERE c.oid = tabdef.oid ),
                        FORMAT( E' (\n    %s\n)', tabdef.cols_create_sql) 
                ),
                case when tabdef.relopts <> '' then format(' WITH (%s)', tabdef.relopts) else '' end,
                coalesce(E'\nPARTITION BY '||pg_get_partkeydef(tabdef.oid), '') 
            ) as table_create_sql
FROM tabdef
$$;

调用:  
select tabledef('模式名','表名');

kingbase强制走索引【需要安装hint插件】

select /*+ index(student ss)*/ * from student where name='xx'

给函数创建索引

create index 索引名 ON 表名(to_char(字段,'格式'))；

sql强行走索引

select /*+ index(student ss)*/ * from student where name='xx'

查询表的所有记录数

SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables 
where schemaname='public'
ORDER BY n_live_tup DESC;

查询结果加一列序列

ROW_NUMBER() over(ORDER bY name DESC ) AS	 num

当前时间的时间戳

select floor(extract(epoch from now()));

获取某个用户下的所有表

select * from pg_tables where TABLEOWNER='用户名';

获取指定年份的所有数据

1、创建year函数之后调用
CREATE OR REPLACE INTERNAL FUNCTION YEAR(dtime DATE) RETURNS BIGINT AS '
BEGIN
 RETURN extract(year from dtime);
END;
' LANGUAGE plsql;

select * from date_test where year(日期字段)='年份';

2、使用to_char函数获年份实现

select * from date_test where to_char(日期字段,'YYYY')='年份';

获取数据库中所有模式的所有表的结构(表名，表注释，字段名，字段类型，是否为空，注释)

SELECT c.relname as 表名,
cast(obj_description(relfilenode,'pg_class') as varchar) AS "表名描述",   a.attname as 字段名, format_type(a.atttypid,a.atttypmod) as 类型, a.attnotnull as 非空, col_description(a.attrelid,a.attnum) as 注释   
FROM pg_class as c,pg_attribute as a inner join pg_type on pg_type.oid = a.atttypid
where  a.attrelid = c.oid and a.attnum>0;

指定模式下指定表的结构（所有表可以把指定表名的条件去掉）

select
col.table_schema,
col.table_name,
col.ordinal_position,
col.column_name,
col.data_type,
col.character_maximum_length,
col.numeric_precision,
col.numeric_scale,
col.is_nullable,
col.column_default,
des.description
from
information_schema.columns col left join pg_description des on
col.table_name::regclass = des.objoid
and col.ordinal_position = des.objsubid
where
table_schema = 'public'
and table_name = 'table_name'
order by
ordinal_position;

查看某个用户下有哪些数据库（u6是用户名）

SELECT a.datname from sys_database a,pg_authid b where a.datdba=b.oid and b.rolname='u6';

兼容mysql的date_format()函数
兼容敏感

create or replace function date_format(para1 timestamp,para2 text) return text

as

declare

form1 text;

begin

form1=replace(para2,'%M','Month');

form1=replace(form1,'%W','Day');

form1=replace(form1,'%D','DDth');

form1=replace(form1,'%Y','YYYY');

form1=replace(form1,'%y','yy');

form1=replace(form1,'%a','Dy');

form1=replace(form1,'%d','DD');

form1=replace(form1,'%e','DD');

form1=replace(form1,'%m','MM');

form1=replace(form1,'%c','MM');

form1=replace(form1,'%b','Mon');

form1=replace(form1,'%j','DDD');

form1=replace(form1,'%H','HH24');

form1=replace(form1,'%k','HH24');

form1=replace(form1,'%h','HH');

form1=replace(form1,'%I','HH');

form1=replace(form1,'%l','HH');

form1=replace(form1,'%i','MI');

form1=replace(form1,'%r','HH:MI:SS');

form1=replace(form1,'%T','HH24:MI:SS');

form1=replace(form1,'%S','SS');

form1=replace(form1,'%s','SS');

form1=replace(form1,'%%','%');

return to_char(para1,form1);

end;

可以进行改写
Mysql：select date_format(CURRENT_TIMSTAMP, ‘%Y-%m-%d %H:%i:%s’)
KES：select to_char (CURRENT_TIMSTAMP, ‘yyyy-mm-dd hh24:mi:ss’)

60、兼容mysql的date_sub函数

create or replace function date_sub(v_date text , v_interval interval)

returns text as $$

declare

  v_rt text;

begin

  select to_char(v_date::timestamp(0) - v_interval,'yyyy-mm-dd hh24:mi:ss' ) into v_rt;

  if length(v_date) = 10 and v_rt like '% 00:00:00' then

    select substr(v_rt,0,10) into v_rt;

  end if;

return v_rt;

end;

$$

LANGUAGE plsql;