MySQL - Database Info

MySQL - Obtaining Database Information

A MySQL server stores two fundamental kinds of information: the data itself (the content of your tables) and metadata. Metadata is ‘data about data’—it describes the structure of your databases, tables, columns, indexes, and server configuration.

Knowing how to query this metadata is a crucial skill for any developer or administrator. It allows you to programmatically inspect your database schema, monitor server status, and understand the results of your queries. You can retrieve three main types of information:

• Query Result Information: Such as the number of rows affected by an INSERT, UPDATE, or DELETE statement.
• Schema Information: The structure of databases and their objects (tables, columns, views, etc.).
• Server Information: The server’s version, current status, and configuration variables.

Getting Information with SHOW Commands

MySQL provides a set of simple, human-readable SHOW commands for quickly inspecting the server from a command-line client.

Get Server Version

You can check the server version from your system’s command line (before logging into MySQL) or by using the VERSION() function within a MySQL session.

# From your OS terminal
C:\> mysql --version
# or
$ mysql -V

This might produce output like:

mysql  Ver 8.0.33 for macos13 on arm64 (MySQL Community Server - GPL)

Alternatively, once connected to MySQL:

SELECT VERSION();

List All Databases

The SHOW DATABASES command lists all databases on the server that you have permission to see.

SHOW DATABASES;

Database
information_schema
mysql
performance_schema
sys
TUTORIALS

Note: information_schema, mysql, performance_schema, and sys are system databases used internally by MySQL.

List Tables in a Database

First, select a database with the USE command, then use SHOW TABLES.

USE TUTORIALS;
SHOW TABLES;

Tables_in_TUTORIALS
CUSTOMERS
EMPLOYEES
PRODUCTS

Describe Table Structure

To see the definition of a table, including its columns, data types, and keys, use DESCRIBE or its shorthand, DESC.

DESCRIBE CUSTOMERS;

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
NAME	varchar(50)	NO		NULL
AGE	int	NO		NULL
ADDRESS	varchar(100)	YES		NULL
SALARY	decimal(18,2)	YES		NULL

A More Standard Approach: The INFORMATION_SCHEMA

While SHOW commands are convenient, they are specific to MySQL. The SQL standard provides a more powerful and portable way to get metadata: the INFORMATION_SCHEMA. This is a special read-only database containing tables that store information about all other databases.

• To list databases: SELECT schema_name FROM INFORMATION_SCHEMA.SCHEMATA;
• To list tables: SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'TUTORIALS';
• To describe a table: SELECT column_name, data_type, is_nullable FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'TUTORIALS' AND table_name = 'CUSTOMERS';

Using the INFORMATION_SCHEMA is considered a best practice for tools and applications that need to be database-agnostic.

Checking Rows Affected by a Query

After running an INSERT, UPDATE, or DELETE query, you often need to know how many rows were changed. All modern database libraries provide a straightforward way to get this count.

Client Library Examples

Here’s how you can get the affected row count in various languages:

/* PHP (mysqli) */
$mysqli->query("UPDATE CUSTOMERS SET SALARY = 7000 WHERE NAME = 'Chaitali'");
$affected_rows = $mysqli->affected_rows;
printf("%d rows were updated.\n", $affected_rows);


/* NodeJS (mysql2/promise) */
const [result] = await connection.execute(
  "UPDATE CUSTOMERS SET SALARY = ? WHERE NAME = ?",
  [7000, 'Chaitali']
);
console.log(`${result.affectedRows} rows were updated.`);


/* Java (JDBC) */
String sql = "UPDATE CUSTOMERS SET SALARY = ? WHERE NAME = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setBigDecimal(1, new BigDecimal("7000.00"));
    pstmt.setString(2, "Chaitali");
    int affectedRows = pstmt.executeUpdate();
    System.out.printf("%d rows were updated.%n", affectedRows);
}


# Python (mysql-connector-python)
update_query = "UPDATE CUSTOMERS SET SALARY = %s WHERE NAME = %s"
cursor.execute(update_query, (7000, 'Chaitali'))
connection.commit() # Don't forget to commit the transaction
print(f"{cursor.rowcount} rows were updated.")

Getting Server Metadata

You can get high-level information about the server’s state and configuration using these commands and functions.

Command / Function	Description
SELECT VERSION()	Returns the full version string of the MySQL server.
SELECT DATABASE()	Returns the name of the current default database, or NULL if none is selected.
SELECT USER()	Returns the username and host of the current client connection.
SHOW STATUS	Provides a long list of server status indicators, useful for performance monitoring.
SHOW VARIABLES	Shows the values of MySQL system variables that control the server’s configuration.