【 使用环境 】测试环境
【 OB or 其他组件 】 OB
【 使用版本 】4.1
【问题描述】无法获取OB 无主键和唯一索引表数据大小
【复现路径】
【问题现象及影响】
select a.TABLE_SCHEMA,a.TABLE_NAME,a.ENGINE,sum(a.data_length+a.index_length) as SPACE_SIZE,a.TABLE_ROWS from
(select TABLE_SCHEMA,TABLE_NAME,ENGINE,DATA_LENGTH,INDEX_LENGTH,TABLE_ROWS from information_schema.tables where TABLE_SCHEMA = 'ztest'
and TABLE_TYPE!='VIEW') a left join (select TABLE_SCHEMA,TABLE_NAME from information_schema.statistics where NON_UNIQUE=0) b on
a.TABLE_SCHEMA=b.TABLE_SCHEMA and a.TABLE_NAME=b.TABLE_NAME where b.TABLE_NAME is null order by a.TABLE_SCHEMA,a.TABLE_ROWS desc;
利用该语句可以获得OB 主键表的数据大小，但是无主键表无法获取其数据大小
表结构
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`num` int(100) DEFAULT NULL,
`num1` int(100) unsigned zerofill DEFAULT NULL,
`sex` enum('男','女') DEFAULT NULL,
`str` set('value1','value2') DEFAULT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`num` int(100) DEFAULT NULL,
`num1` int(100) unsigned zerofill DEFAULT NULL,
`sex` enum('男','女') DEFAULT NULL,
`str` set('value1','value2') DEFAULT NULL
)
测试结果
obclient [ztest]> select * from information_schema.tables where TABLE_SCHEMA = 'ztest' and TABLE_TYPE!='VIEW';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
| def | ztest | t1 | BASE TABLE | NULL | NULL | NULL | 3 | 103 | 309 | NULL | NULL | NULL | NULL | 2023-11-02 15:07:56 | 2023-11-02 16:21:13 | NULL | utf8mb4_general_ci | NULL | NULL | |
| def | ztest | employees | BASE TABLE | NULL | NULL | NULL | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2023-11-07 17:20:14 | 2023-11-07 17:20:14 | NULL | utf8mb4_general_ci | NULL | NULL | |
| def | ztest | emp_msg | BASE TABLE | NULL | NULL | NULL | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2023-11-07 17:20:33 | 2023-11-07 17:20:33 | NULL | utf8mb4_general_ci | NULL | NULL | |
| def | ztest | test | BASE TABLE | NULL | NULL | NULL | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2023-11-07 17:24:35 | 2023-11-07 17:24:41 | NULL | utf8mb4_general_ci | NULL | NULL | |
| def | ztest | t | BASE TABLE | NULL | NULL | NULL | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2023-11-07 17:24:57 | 2023-11-07 17:24:57 | NULL | utf8mb4_general_ci | NULL | NULL | |
| def | ztest | msg | BASE TABLE | NULL | NULL | NULL | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2023-11-07 17:24:57 | 2023-11-07 17:24:57 | NULL | utf8mb4_general_ci | NULL | NULL | |
| def | ztest | city | BASE TABLE | NULL | NULL | NULL | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2023-11-08 09:38:56 | 2023-11-08 09:38:56 | NULL | utf8mb4_general_ci | NULL | NULL | |
| def | ztest | t2 | BASE TABLE | NULL | NULL | NULL | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2023-11-09 18:15:11 | 2023-11-09 18:15:11 | NULL | utf8mb4_general_ci | NULL | NULL | |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
8 rows in set (0.050 sec)
obclient [ztest]> select * from t2;
+----+------+------+------------------------------------------------------------------------------------------------------+------+------+
| id | name | num | num1 | sex | str |
+----+------+------+------------------------------------------------------------------------------------------------------+------+------+
| 1 | 11 | 123 | 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000123 | NULL | NULL |
| 2 | 22 | 123 | 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000123 | 男 | NULL |
| 3 | 33 | 123 | 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000123 | 男 | NULL |
+----+------+------+------------------------------------------------------------------------------------------------------+------+------+
3 rows in set (0.023 sec)
obclient [ztest]> select * from t1;
+----+------+------+------------------------------------------------------------------------------------------------------+------+------+
| id | name | num | num1 | sex | str |
+----+------+------+------------------------------------------------------------------------------------------------------+------+------+
| 1 | 11 | 123 | 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000123 | NULL | NULL |
| 2 | 22 | 123 | 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000123 | 男 | NULL |
| 3 | 33 | 123 | 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000123 | 男 | NULL |
+----+------+------+------------------------------------------------------------------------------------------------------+------+------+
3 rows in set (0.001 sec)
【附件】