MySQL-এ টেবিল অস্তিত্ব আছে কিনা চেক করা (INFORMATION_SCHEMA.TABLES)

MySQL-এ কোনো টেবিল ডেটাবেসে আছে কিনা চেক করতে INFORMATION_SCHEMA.TABLES ক্যোয়ারি করুন। এটি SHOW TABLES LIKE 'name'-এর চেয়ে বেশি ইনফো দেয়।

সিনট্যাক্স

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'database_name' 
  AND TABLE_NAME = 'table_name';

উদাহরণ

CREATE TABLE client_information (
    id INT,
    name VARCHAR(10)
);

INSERT INTO client_information VALUES (1, 'Larry'), (2, 'Mike'), (3, 'Sam');

টেবিল আছে কিনা চেক

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = 'client_information';

ফলাফল (২টি ডেটাবেসে একই নামের টেবিল)

+---------------+--------------+--------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
| 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     |                |               |
+---------------+--------------+--------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+

বুলিয়ান চেক (EXISTS স্টাইল)

SELECT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'client_information'
) AS table_exists;

ফলাফল

+--------------+
| table_exists |
+--------------+
| 1            |
+--------------+

বিকল্প পদ্ধতিগুলো

SHOW TABLES (সহজ কিন্তু কম ইনফো)

SHOW TABLES LIKE 'client_information';

SHOW TABLE STATUS (বেশি মেটাডেটা)

SHOW TABLE STATUS LIKE 'client_information';\G

ফাংশন/প্রসিডিউর থেকে চেক (প্রোগ্রাম্যাটিক)

DELIMITER //
CREATE PROCEDURE table_exists(IN db_name VARCHAR(64), IN tbl_name VARCHAR(64), OUT exists_bool BOOL)
BEGIN
    SELECT COUNT(*) INTO exists_bool
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = db_name AND TABLE_NAME = tbl_name;
END //
DELIMITER ;

CALL table_exists('test', 'client_information', @exists);
SELECT @exists;

দ্রষ্টব্য

• TABLE_TYPE: BASE TABLE (নরমাল), VIEW, SYSTEM VIEW
• ENGINE: InnoDB, MyISAM, MEMORY, etc.
• TABLE_ROWS InnoDB-এ আনুমানিক (একজেক্ট নয়)