--- title: information_schema.COLUMNS 表查询结果重复-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 information_schema.COLUMNS 表查询结果重复相关的常见问题和使用技巧，帮助您快速解决 information_schema.COLUMNS 表查询结果重复的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # information_schema.COLUMNS 表查询结果重复 更新时间：2023-11-14 03:16 适用版本： V2.2.x、V3.1.x、V3.2.x 内容类型：Troubleshoot ## 问题描述 information_schema.COLUMNS 表通过 COLUMN_NAME in ('zone','a','b')过滤出重复行，与 in 内的条件个数相关。 1. 当查询语句 column_name 在 in 中列条件个数为 1个。 ```shell obclient > SELECT * FROM information_schema.COLUMNS WHERE table_schema = 'oceanbase' AND table_name = '__all_server' and column_name in ('zone'); ``` 输出结果为: ```shell +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | GENERATION_EXPRESSION | +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+ | def | oceanbase | __all_server | zone | 6 | NULL | NO | varchar | 128 | 512 | NULL | NULL | NULL | utf8mb4 | utf8mb4_general_ci | varchar(128) | | | NULL | | | +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+ 1 row in set (0.00 sec) ``` 2. 当查询语句 column_name 在 in 中列条件个数为 2个。 ```shell obclient > SELECT * FROM information_schema.COLUMNS WHERE table_schema = 'oceanbase' AND table_name = '__all_server' AND column_name in ('zone','a'); ``` 输出结果如下: ```shell +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | GENERATION_EXPRESSION | +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+ | def | oceanbase | __all_server | zone | 6 | NULL | NO | varchar | 128 | 512 | NULL | NULL | NULL | utf8mb4 | utf8mb4_general_ci | varchar(128) | | | NULL | | | | def | oceanbase | __all_server | zone | 6 | NULL | NO | varchar | 128 | 512 | NULL | NULL | NULL | utf8mb4 | utf8mb4_general_ci | varchar(128) | | | NULL | | | +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+ 2 rows in set (0.00 sec) ``` 3. 当查询语句 column_name 在 in 中列条件个数为 3个。 ```shell obclient > SELECT * FROM information_schema.COLUMNS WHERE table_schema = 'oceanbase' AND table_name = '__all_server' AND column_name in ('zone','a','b'); ``` 输出结果如下: ```shell +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | GENERATION_EXPRESSION | +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+ | def | oceanbase | __all_server | zone | 6 | NULL | NO | varchar | 128 | 512 | NULL | NULL | NULL | utf8mb4 | utf8mb4_general_ci | varchar(128) | | | NULL | | | | def | oceanbase | __all_server | zone | 6 | NULL | NO | varchar | 128 | 512 | NULL | NULL | NULL | utf8mb4 | utf8mb4_general_ci | varchar(128) | | | NULL | | | | def | oceanbase | __all_server | zone | 6 | NULL | NO | varchar | 128 | 512 | NULL | NULL | NULL | utf8mb4 | utf8mb4_general_ci | varchar(128) | | | NULL | | | +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+ 3 rows in set (0.00 sec) ``` ## 适用版本 OceanBase 数据库 V2.2.x 、V3.2.x 版本。 ## 问题原因 `information_schema.COLUMNS` 表的主键列包括 `db_name`、`table_name`、`column_name`，但实际上这张虚表的实现中只基于 `db_name` 和 `table_name` 实现了索引，因此对于 `column_name` 实际上还是个遍历的逻辑，这就导致 当优化器抽出 query range 为 [oceanbase, __all_server, zone] 以及 [ oceanbase, __all_server, a] 两个 range 时， 不仅 [oceanbase, __all_server, zone] 会扫描出 [oceanbase, all_server] 前缀的所有行，[oceanbase, __all_server, a] 也会扫描出包含 [oceanbase, all_server] 前缀的所有行，这就导致了 [oceanbase, __all_server, zone] 出现了两次，[oceanbase, __all_server, a,b] 也会扫描出包含 [oceanbase, all_server] 前缀的所有行，这就导致了 [oceanbase, __all_server, zone] 出现了三次，尽管优化器在该 Table scan 上保留了 Filter 作为兜底。但因为产生了两份相同数据，因此结果错误。 ## 解决方法 升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库企业版 V3.2.4 BP3 Hotfix2 (oceanbase-3.2.4.3-103020012023051810) 及以上版本。 #### 注意 OceanBase 数据库 V4.1.x、V3.1.x 版本不存在该问题， 因为在这些版本中 `information_schema.COLUMNS` 表的主键列只有 `db_name`、`table_name`。 Previous [Oracle 模式下使用 create or replace 创建对象后对象上的权限会丢失](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000180193) Next [OceanBase 数据库 Oracle 模式下授予其它用户访问视图的权限报错 -5231](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000267158) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线