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

1. 数据库的启停

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

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敏感  )
1
2
3

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

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

4.设置返回字段大写

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

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

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

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

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

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

SELECT sys_cancel_backend(pid);
1

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

修改目录权限
1

9.windows中54321端口被占用

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

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

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

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

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

12. 配置 ’ ’ 代替 null

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

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

conn.setAutoCommit(false); 
1

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
1
2
3
4
5
6
7

15.模糊查询字段

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

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

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

17.查看事务隔离级别

SHOW TRANSACTION ISOLATION KINGBASE_INTERNAL_LEVEL; 
1

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

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

19.非交互式还原数据库

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

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

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

21.授权序列

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

22.查看当前账号会话数

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

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

set ora_enable_varchar_match_bpchar to on
1

24.查看表的存储路径

select sys_relation_filepath('表名');
1

25.查看会话进程

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

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

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

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

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

28.查看sql的执行时间

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

29.两表关联更新字段

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

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

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

31.时间戳转换为时间

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

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) ;
1
2
3
4
5
6
7
8
9

33.生成UUID的命令

select sys_guid();
1

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

DATE_FORMAT(date TIMESTAMP,format TEXT);
1

35.初始化编码问题

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

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

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

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

to_number('12345','99999') 
1

38.实现mysql的date_add功能

select current_date + interval '0 hours'; 
1

39. 获取月份的函数

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

40. 查看所有的序列属性

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

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

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 = '你的表名';  
1
2
3
4
5
6
7
8
9
10
11
12

42. 设置中文占多少字节

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

43.查看字段类型

select sys_typeof(  name )  from aa
1

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

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

45. 查询所有的函数

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) 
1
2
3
4
5
6
7

46. 查看所有的函数，类型，拥有者（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;  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

47.用sql命令查看ddl语句

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

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

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

49. 给函数创建索引

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