[大数据]MySQL && OpenGauss 表元数据查询总结

在MYSQL中，查询用户表信息和表字段信息，在mysql的 information_schema库中tables 表和columns表中进行定义，我们可以直接查询。

select table_name tableName , table_rows tableRows from information_schema.tables  WHERE table_schema=?

select table_name tableName ,column_name  columnName, ordinal_position ordinalPosition, data_type dataType, column_type columnType,column_key columnKey from information_schema.columns where table_name in (:tableNames)

但是在OpenGauss中，我们不能直接查询这些信息，需要根据openGauss提供的一些视图自己组装SQL

information_schema.tables 表信息

information_schema.columns 表列信息

information_schema.key_column_usage 约束对应字段信息

information_schema.table_constraints 表约束信息

select table_name tableName  from information_schema.tables  WHERE  table_schema=? and TABLE_TYPE='BASE TABLE';

select c.table_name tableName, c.column_name columnName, c.ordinal_position ordinalPosition, c.data_type dataType, pkc.column_key
from information_schema.columns c
left join (select kcu.table_name, kcu.column_name, 'PRI' column_key
             from information_schema.key_column_usage kcu
            WHERE kcu.constraint_name in(select constraint_name
                                           from information_schema.table_constraints tc
                                          where tc.constraint_schema = 'jack'
                                            and tc.constraint_type = 'PRIMARY KEY') ) pkc on c.table_name = pkc.table_name
                 and c.column_name = pkc.column_name
 where c.table_schema = 'jack' and c.table_name = 'client'

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖