Purpose of TABLE_CATALOG Column in INFORMATION_SCHEMA.TABLES

information-schemaMySQL

In MySQL's table INFORMATION_SCHEMA.TABLES, there's a column named 'TABLE_CATALOG'. The documentation is sparse on this column and I'm wondering what the heck is the purpose of this? Any killer-app purpose or something that I'm missing?

mysql> SHOW CREATE TABLE INFORMATION_SCHEMA.TABLES\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Best Answer

That column exists for compatibility with other databases.

Interestingly, here is information_schema.tables in PostgreSQL

postgres=# \d information_schema.tables
                       View "information_schema.tables"
            Column            |               Type                | Modifiers
------------------------------+-----------------------------------+-----------
 table_catalog                | information_schema.sql_identifier |
 table_schema                 | information_schema.sql_identifier |
 table_name                   | information_schema.sql_identifier |
 table_type                   | information_schema.character_data |
 self_referencing_column_name | information_schema.sql_identifier |
 reference_generation         | information_schema.character_data |
 user_defined_type_catalog    | information_schema.sql_identifier |
 user_defined_type_schema     | information_schema.sql_identifier |
 user_defined_type_name       | information_schema.sql_identifier |
 is_insertable_into           | information_schema.character_data |
 is_typed                     | information_schema.character_data |
 commit_action                | information_schema.character_data |
View definition:
 SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text
            WHEN c.relkind = 'r'::"char" THEN 'BASE TABLE'::text
            WHEN c.relkind = 'v'::"char" THEN 'VIEW'::text
            ELSE NULL::text
        END::information_schema.character_data AS table_type, NULL::character varying::information_schema.sql_identifier AS self_referencing_column_name, NULL::character varying::information_schema.character_data AS reference_generation, NULL::character varying::information_schema.sql_identifier AS user_defined_type_catalog, NULL::character varying::information_schema.sql_identifier AS user_defined_type_schema, NULL::character varying::information_schema.sql_identifier AS user_defined_type_name,
        CASE
            WHEN c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" AND (EXISTS ( SELECT 1
               FROM pg_rewrite
              WHERE pg_rewrite.ev_class = c.oid AND pg_rewrite.ev_type = '3'::"char" AND pg_rewrite.is_instead)) THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.character_data AS is_insertable_into, 'NO'::character varying::information_schema.character_data AS is_typed,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE'::text
            ELSE NULL::text
        END::information_schema.character_data AS commit_action
   FROM pg_namespace nc, pg_class c
  WHERE c.relnamespace = nc.oid AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char"])) AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));

In PostgreSQL, the table_catalog field actually means something. In fact, it is part of the SQL-92 standard. The catalog of a database groups related metadata for a database. See, in PostgreSQL, the information_schema is only for the database you are connected to. In MySQL, this concept is blurred and the information_schema contains all metadata for all databases, especially if you have ALL PRIVILEGES on *.* as with root@localhost.

It makes sense that it is there in MySQL but blank. The information_schema in MySQL will collapse for you if your DB user has ALL PRIVILEGES on db.* instead of *.*. In PostgreSQL, the collapse is automatic when you connect to a database schema. So, the table_catalog column is just there for MySQL to be SQL-92 compatible and nothing more.

Related Question