mysql表結構表空間和索引的查詢 1.查詢表的結構信息 Sql代碼 desc tableName; show columns from tableName; describe tableName 上面的結果返回的結果是一樣的。 2 查詢表的列信息。 Sql代碼 select * from information_schema.columns where table_name='tableName'; 3 查看庫中所有的庫 Sql代碼 SELECT LOWER(schema_name) schema_name FROM information_schema.schemata WHERE schema_name NOT IN ( 'mysql', 'information_schema', 'test' ) 4 查詢某個庫中所有的表 Sql代碼 SELECT table_name, create_time updated_at, table_type, ENGINE, table_rows num_rows, table_comment, CEIL(data_length / 1024 / 1024) store_capacity FROM information_schema.TABLES WHERE table_schema = 'schema_name' AND table_name NOT LIKE 'tmp#_%' ESCAPE '#' 5 查看某一個庫下某一個表的所有字段 Sql代碼 SELECT lower(column_name) column_name, ordinal_position position, column_default dafault_value, substring(is_nullable, 1, 1) nullable, column_type data_type, column_comment, character_maximum_length data_length, numeric_precision data_precision, numeric_scale data_scale FROM information_schema.COLUMNS WHERE table_schema = 'admin_portal' AND table_name = 'ap_epiboly_task'; 6 查看某一個庫下某一張表的索引 Sql代碼 <strong>SELECT DISTINCT lower(index_name) index_name, lower(index_type) type FROM information_schema.statistics WHERE table_schema = 'employees' AND table_name = 'employees';</strong> 7 查看某一個庫下某一個表的注釋 Sql代碼 SELECT table_comment comments FROM information_schema.TABLES WHERE table_schema = 'employees' AND table_name = 'employees'; 8 1.查看索引 （1）單位是GB SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'test'; +------------------+ | Total Index Size | +------------------+ | 1.70 GB | +------------------+ （2）單位是MB SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'test'; 其中“database”為你所要查看的數據庫 2.查看表空間 SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), ' GB') AS 'Total Data Size' FROM information_schema.TABLES WHERE table_schema LIKE 'test'; +-----------------+ | Total Data Size | +-----------------+ | 3.01 GB | +-----------------+ 3.查看數據庫中所有表的信息 SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,2),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),2),'G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),2),'G') AS 'Index Size' , CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'test';
