Show indexes for a table with the MySQL INFORMATION_SCHEMA

I've previously posted how to get the indexes for a MySQL table using a "SHOW INDEXES" SQL query and in this post show an alternative way to get the indexes for a table using MySQL's INFORMATION_SCHEMA.

Example tables

The examples in this post uses a table structured as follows:

CREATE TABLE `products` (
  `product_id` int(10) unsigned NOT NULL auto_increment,
  `url` varchar(100) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `visible` tinyint(1) unsigned NOT NULL default '1',
  PRIMARY KEY  (`product_id`),
  UNIQUE KEY `url` (`url`),
  KEY `visible` (`visible`)
)

Get the indexes

This first query will get all the column name and the index type for all columns that are indexed, from the "test" database and "products" table:

SELECT COLUMN_NAME, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test'
AND TABLE_NAME = 'products'
AND COLUMN_KEY != ''

For the example table this will return the following:

+-------------+------------+
| COLUMN_NAME | COLUMN_KEY |
+-------------+------------+
| product_id  | PRI        |
| url         | UNI        |
| visible     | MUL        |
+-------------+------------+

PRI indicates it is a primary key; UNI that it is indexed with a unique constraint; and MUL that it is a regular index.

Get the primary key(s)

The second example shows how to just get the primary keys from a table using the INFORMATION_SCHEMA, using the example "products" table from the "test" database again:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test'
AND TABLE_NAME = 'products'
AND COLUMN_KEY = 'PRI'

For the example table this would return:

+-------------+
| COLUMN_NAME |
+-------------+
| product_id  |
+-------------+

Related posts:

• Get a MySQL table structure from the INFORMATION_SCHEMA
• Show indexes for a table with MySQL

Get a MySQL table structure from the INFORMATION_SCHEMA

There are at least two ways to get a MySQL table's structure using SQL queries. The first is using DESCRIBE (which I have already covered in an earlier post) and the second by querying the INFORMATION_SCHEMA. This post deals with querying the INFORMATION_SCHEMA which has more information available than using DESCRIBE.

Example table

The example table used in this post was created with the following SQL, and is the same as in the earlier post:

CREATE TABLE `products` (
`product_id` int(10) unsigned NOT NULL auto_increment,
`url` varchar(100) NOT NULL,
`name` varchar(50) NOT NULL,
`description` varchar(255) NOT NULL,
`price` decimal(10,2) NOT NULL,
`visible` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`product_id`),
UNIQUE KEY `url` (`url`),
KEY `visible` (`visible`)
)

Using the INFORMATION SCHEMA

The SQL query to get the table structure from the INFORMATION SCHEMA is as follows, where the database name is "test" and the table name is "products":

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test'
AND TABLE_NAME = 'products';

You can run this from the MySQL CLI; phpMyAdmin; or using a programming language like PHP and then using the functions to retrieve each row from the query.

The resulting data from the MySQL CLI looks like this for the example table above:

+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+---------------------+------------+----------------+---------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE         | COLUMN_KEY | EXTRA          | PRIVILEGES                      | COLUMN_COMMENT |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+---------------------+------------+----------------+---------------------------------+----------------+
| NULL          | test         | products   | product_id  |                1 | NULL           | NO          | int       |                     NULL |                   NULL |                10 |             0 | NULL               | NULL              | int(10) unsigned    | PRI        | auto_increment | select,insert,update,references |                |
| NULL          | test         | products   | url         |                2 | NULL           | NO          | varchar   |                      100 |                    100 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(100)        | UNI        |                | select,insert,update,references |                |
| NULL          | test         | products   | name        |                3 | NULL           | NO          | varchar   |                       50 |                     50 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(50)         |            |                | select,insert,update,references |                |
| NULL          | test         | products   | description |                4 | NULL           | NO          | varchar   |                      255 |                    255 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(255)        |            |                | select,insert,update,references |                |
| NULL          | test         | products   | price       |                5 | NULL           | NO          | decimal   |                     NULL |                   NULL |                10 |             2 | NULL               | NULL              | decimal(10,2)       |            |                | select,insert,update,references |                |
| NULL          | test         | products   | visible     |                6 | 1              | NO          | tinyint   |                     NULL |                   NULL |                 3 |             0 | NULL               | NULL              | tinyint(1) unsigned | MUL        |                | select,insert,update,references |                |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+---------------------+------------+----------------+---------------------------------+----------------+

As you can see there is a lot of information available.

Related posts:

• MySQL: SQL to drop a column from a table
• Get a MySQL table structure with DESCRIBE
• Find which tables use INNODB with MySQL
• Show indexes for a table with MySQL
• How to tell which storage engine a MySQL table uses
• Check if a MySQL table exists

Mysql: Mass Find and Replace MySQL

<?php
/*

* MySQL Mass Find and Replace

*/


// Connect to your MySQL database.
$hostname = "localhost";
$username = "root";
$password = "password";
$database = "db_name";
mysql_connect($hostname, $username, $password);

// The find and replace strings.
$find = "what_i_need_to_find";
$replace = "what_i_need_to_replace_with";

// Test mode
$test_mode = false;

// Loop through all tables and columns
$loop = mysql_query("

    SELECT
        concat('UPDATE ',table_schema,'.',table_name, ' SET ',column_name, '=replace(',column_name,', ''{$find}'', ''{$replace}'');') AS s
    FROM
        information_schema.columns
    WHERE
        table_schema = '{$database}'")
or die ('Cannot loop through database fields: ' . mysql_error());

while ($query = mysql_fetch_assoc($loop))
{
    if ($test_mode)
        echo "{$query['s']}<br/>";
    else
       mysql_query($query['s']);
} 
?>