MySQL使用小记

生成随机数

drop function if exists rand_num;
create function rand_num(start_num integer, end_num integer ) 
returns integer
begin
  return floor(start_num + rand() * (end_num - start_num + 1));
end;

select rand_num(1, 9);

生成数据字典

• mysql+rds数据字典导出

select tb.table_schema as 数据库名称
 , tb.table_name as 表名
 , tb.comments as 表描述
 , tc.column_name as 字段名
 , tc.column_comment as 字段描述
 , tc.data_type as 数据类型
 , tc.column_type as 字段类型
 , tc.column_key as 键值
 , tc.ordinal_position as 字段排序
from (
 select table_schema
  , table_name
  , table_comment as comments
 from information_schema.tables 
 where table_schema not in ('information_schema','mysql')
) tb 
join (
 select table_schema
  , table_name
  , column_name
  , data_type
  , column_type
  , column_key
  , column_comment
  , ordinal_position
 from information_schema.columns
) tc on tb.table_schema=tc.table_schema and tb.table_name=tc.table_name
order by tb.table_schema,tb.table_name,tc.ordinal_position
;

• adb生成数据字典

select 'adb' 数据库类型
    , table_schema 数据库名称
    , table_name 表名
    , column_name 字段名称
    , data_type 字段类型
    , column_comment 字段备注
    , ordinal_position+1 as 字段排序
    , case when is_primarykey=1 then 'pri' else null end as 字段标识
from information_schema.columns
order by table_schema
    , table_name
    , ordinal_position
;

• adb转mysql语法建表语句

-- adb转mysql语法建表语句
-- 数据字典
select concat(
        case when tc.ordinal_position=1 then concat('drop table if exists ',tb.table_name,';\r\n', 'create table ',tb.table_name,' (\r\n    ') else '  , ' end
      , concat(tc.column_name,' ',tc.column_type,' comment ''',tc.column_comment,'''')
      , case when tc.ordinal_position=mx.ordinal_position then concat('\r\n  , primary key(',mx.ck,')\r\n',') ', 'comment ''',tb.comments,''';\r\n') else '' end
    ) as _sql
  , tb.table_schema
  , tb.table_group
  , tb.table_name
  , tb.comments
  , tc.column_name
  , tc.data_type
  , tc.column_comment
  , tc.is_primarykey
  , tc.ordinal_position
  , mx.ck
from (
  select table_schema
    , table_group
    , table_name
    , comments
  from information_schema.tables 
) tb 
join (
  select table_schema
    , table_group
    , table_name
    , column_name
    , data_type
    , type_code
    , sub_type_code
    , case when data_type in ('timestamp','date') then data_type
           when numeric_precision<>-1 then concat(data_type,'(',numeric_precision,',',numeric_scale,')') 
      else concat(data_type,'(',type_code,')') end as column_type
    , column_comment
    , is_primarykey
    , ordinal_position+1 as ordinal_position
  from information_schema.columns
) tc on tb.table_schema=tc.table_schema and tb.table_group=tc.table_group and tb.table_name=tc.table_name
join (
  select table_schema
    , table_group
    , table_name
    , max(ordinal_position)+1 as ordinal_position
    , group_concat(case when is_primarykey=1 then column_name else null end) as ck
  from information_schema.columns
  group by table_schema
    , table_group
    , table_name
) mx on tb.table_schema=mx.table_schema and tb.table_group=mx.table_group and tb.table_name=mx.table_name
order by tb.table_schema,tb.table_name,tc.ordinal_position
;

• drds元数据信息获取

select table_schema
  , table_name
  , max(table_comment) as table_comment
  , count(1) as sub_table_count
  , sum(table_rows) as table_rows
  , round(sum(data_length+index_length)/1024/1024/1024,4) as total_gb_size
  , sum(data_length+index_length) as total_length
  , sum(data_length) as data_length
  , sum(index_length) as index_length
from (
  /*+tddl:scan()*/ 
  select case when cast(substring_index(table_schema,'_',-1) as unsigned)>0 or substring_index(table_schema,'_',-1) in ('00','000','0000') then replace(table_schema,concat('_',substring_index(table_schema,'_',-2)),'') else table_schema end table_schema
    , table_schema as table_schema_old
    , case when cast(substring_index(table_name,'_',-1) as unsigned)>0 or substring_index(table_name,'_',-1) in ('00','000','0000') then replace(table_name,concat('_',substring_index(table_name,'_',-2)),'') 
           when cast(substring_index(table_schema,'_',-1) as unsigned)>0 or substring_index(table_schema,'_',-1) in ('00','000','0000') then replace(table_name,concat('_',substring_index(table_name,'_',-1)),'') 
      else table_name end table_name
    , table_name as table_name_old
    , table_comment
    , table_type
    , table_rows
    , data_length
    , index_length
  from information_schema.tables a
  where table_schema not in ('mysql','information_schema')
    and table_name not like '__drds__%'
) t 
group by table_schema,table_name
;