home

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

ตรวจสอบว่าตารางมีอยู่ใน MySQL หรือไม่

เช็ค Table Exists ด้วย INFORMATION_SCHEMA.TABLES

Query INFORMATION_SCHEMA.TABLES ด้วย TABLE_NAME และ TABLE_SCHEMA

คำสั่ง

SELECT * FROM information_schema.tables 
WHERE table_name = 'Client_information';

ตัวอย่างสร้างตาราง

CREATE TABLE Client_information (Id INT, Name VARCHAR(10));
INSERT INTO Client_information VALUES (1, 'Larry'), (2, 'Mike'), (3, 'Sam');

ผลลัพธ์ Query

+---------------+--------------+--------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
| 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           | sample       | client_information | BASE TABLE | InnoDB | 10      | Dynamic    | 4          | 4096           | 16384       | 0               | 0            | 0         | NULL           | 2019-03-11 20:02:40 | NULL                | NULL       | utf8_general_ci    | NULL     |                |               |
| def           | test         | client_information | BASE TABLE | InnoDB | 10      | Dynamic    | 3          | 5461           | 16384       | 0               | 0            | 0         | NULL           | 2019-04-03 02:28:31 | 2019-04-03 02:29:00 | NULL       | utf8mb4_0900_ai_ci | NULL     |                |               |
+---------------+--------------+--------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+

วิธีอื่น

  • SHOW TABLES LIKE 'Client_information' - ง่ายกว่า แต่อยู่ใน Database ปัจจุบัน
  • SHOW TABLES FROM db_name LIKE 'table_name' - ระบุ Database
  • SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'db' AND table_name = 'table' - คืน 1/0
  • EXISTS (SELECT 1 FROM information_schema.tables WHERE ...) - ใช้ใน IF/Stored Procedure

ใน Application Code

-- Java/JDBC
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs = meta.getTables(null, "db_name", "table_name", null);
boolean exists = rs.next();