home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> การเขียนโปรแกรม >> MySQL

รัน Query ทุกตารางใน Database ด้วย INFORMATION_SCHEMA.TABLES

เมื่อต้องการรัน Query เดียวที่ครอบคลุมทุกตารางใน Database (เช่น นับแถวรวมทั้ง DB) ให้ Query INFORMATION_SCHEMA.TABLES ซึ่งเก็บ Metadata ของทุกตาราง

Syntax: นับแถวรวมทุกตารางใน Database ปัจจุบัน

SELECT SUM(TABLE_ROWS) AS Total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();

ผลลัพธ์ตัวอย่าง

+-------+
| Total |
+-------+
| 1666  |
+-------+

คำอธิบาย

  • INFORMATION_SCHEMA.TABLES - View เมตาดาต้าตารางทั้งหมด
  • TABLE_SCHEMA - ชื่อ Database (Schema)
  • DATABASE() - ฟังก์ชันคืน Database ปัจจุบัน
  • TABLE_ROWS - จำนวนแถวประมาณการ (Estimated) สำหรับ InnoDB
  • SUM(TABLE_ROWS) - รวมจำนวนแถวทุกตาราง

ข้อควรระวัง: TABLE_ROWS เป็นประมาณการ

  • InnoDB: ประมาณการ (Estimate) ไม่ใช่ Exact Count
  • MyISAM: Exact (เก็บไว้ใน Header)
  • ต้องการ Exact Count: SELECT COUNT(*) FROM table_name ต่อตาราง

Query ที่มีประโยชน์อื่นๆ

รายชื่อตาราง + จำนวนแถว + Engine + Size

SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

หาตารางว่าง (0 Rows)

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_ROWS = 0;

สร้าง Dynamic SQL สำหรับทุกตาราง

SELECT CONCAT('ANALYZE TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;') AS sql_cmd
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db';

Dynamic SQL จริง (ต้องใช้ Prepared Statement)

SET @sql = (SELECT GROUP_CONCAT(CONCAT('ANALYZE TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') SEPARATOR '; ')
            FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE());
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

สิทธิ์

  • ต้องมี SELECT บน INFORMATION_SCHEMA
  • User ปกติเข้าถึงได้ (Metadata Visibility ตามสิทธิ์)