MySQL Metadatos

Los metadatos de MySQL son información sobre la base de datos y sus objetos (como tablas, columnas, índices, etc.).

Los metadatos se almacenan en tablas del sistema, que se encuentran en la base de datos information_schema de MySQL. Consultando estas tablas del sistema, puedes obtener información detallada sobre la estructura de la base de datos, los objetos y otra información relacionada.

Es posible que desees conocer los siguientes tres tipos de información de MySQL:

  • Información de resultados de consultas:El número de registros afectados por sentencias SELECT, UPDATE o DELETE.
  • Información de bases de datos y tablas:Contiene información estructural de la base de datos y sus tablas.
  • Información del servidor MySQL:Contiene el estado actual del servidor de base de datos, número de versión, etc.

En el prompt de comandos de MySQL, podemos obtener fácilmente la información del servidor anterior, pero si usas lenguajes de script como Perl o PHP, necesitas llamar a funciones de interfaz específicas para obtenerla; a continuación lo detallaremos.

A continuación se presentan algunas consultas comunes de metadatos de MySQL:

Ver todas las bases de datos:

SHOW DATABASES;

Seleccionar base de datos:

USE database_name;

Ver todas las tablas de una base de datos:

SHOW TABLES;

Ver la estructura de una tabla:

DESC table_name;

Ver los índices de una tabla:

SHOW INDEX FROM table_name;

Ver la sentencia de creación de una tabla:

SHOW CREATE TABLE table_name;

Ver el número de filas de una tabla:

SELECT COUNT(*) FROM table_name;

Ver información de las columnas:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';

En las sentencias SQL anteriores, 'your_database_name' y 'your_table_name' son el nombre de tu base de datos y el nombre de tu tabla, respectivamente.

Ver información de claves foráneas:

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    TABLE_SCHEMA = 'your_database_name'
    AND TABLE_NAME = 'your_table_name'
    AND REFERENCED_TABLE_NAME IS NOT NULL;

Reemplaza 'your_database_name' y 'your_table_name' en las sentencias SQL anteriores por el nombre real de la base de datos y de la tabla.

Base de datos information_schema

information_schema es una base de datos del sistema dentro de MySQL. Contiene información de metadatos sobre el servidor de base de datos, almacenada en forma de tablas en la base de datos information_schema.

Tabla SCHEMATA

Almacena información sobre las bases de datos, como nombre de la base de datos, conjunto de caracteres, reglas de ordenamiento, etc.

SELECT * FROM information_schema.SCHEMATA;

Tabla TABLES

Contiene información sobre todas las tablas de la base de datos, como nombre de la tabla, nombre de la base de datos, motor, número de filas, etc.

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name';

Tabla COLUMNS

Contiene información sobre las columnas de las tablas, como nombre de la columna, tipo de datos, si permite NULL, etc.

SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

Tabla STATISTICS

Proporciona información estadística sobre los índices de las tablas, como nombre del índice, nombre de la columna, unicidad, etc.

SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

Tabla KEY_COLUMN_USAGE

Contiene información sobre las claves foráneas en las tablas, como nombre de la clave foránea, nombre de la columna, tabla relacionada, etc.

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

Tabla REFERENTIAL_CONSTRAINTS

Almacena información sobre las restricciones de clave foránea, como nombre de la restricción, tabla relacionada, etc.

SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

Estas tablas ofrecen abundante información de metadatos que puede utilizarse para consultar la estructura de la base de datos, información de tablas, de columnas, de índices, etc.

Ten en cuenta que debes reemplazar 'your_database_name' y 'your_table_name' en las consultas por el nombre real de la base de datos y de la tabla.

Obtener el número de registros afectados por una consulta

Ejemplo en PERL

En un script DBI, el número de registros afectados por una sentencia se devuelve mediante las funciones do( ) o execute( ):

# 方法 1
# 使用do( ) 执行  $query 
my $count = $dbh->do ($query);
# 如果发生错误会输出 0
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);

# 方法 2
# 使用prepare( ) 及 execute( ) 执行  $query 
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);

Ejemplo en PHP

En PHP, puedes usar la función mysqli_affected_rows( ) para obtener el número de registros afectados por una consulta.

$result_id = mysqli_query ($conn_id, $query);
# 如果查询失败返回 
$count = ($result_id ? mysqli_affected_rows ($conn_id) : 0);
print ("$count 条数据被影响\n");

Lista de bases de datos y tablas

Puedes obtener fácilmente la lista de bases de datos y tablas en el servidor MySQL. Si no tienes permisos suficientes, el resultado devolverá null.

También puedes usar las sentencias SHOW TABLES o SHOW DATABASES para obtener la lista de bases de datos y tablas.

Ejemplo en PERL

# 获取当前数据库中所有可用的表。
my @tables = $dbh->tables ( );
foreach $table (@tables ){
   print "表名 $table\n";
}

Ejemplo en PHP

El siguiente ejemplo muestra todas las bases de datos del servidor MySQL:

Ver todas las bases de datos

<?php $dbhost = 'localhost'; //Dirección del host del servidor MySQL $dbuser = 'root'; //Nombre de usuario de MySQL $dbpass = '123456'; //Contraseña del usuario de MySQL $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Error de conexión:' . mysqli_error($conn)); } //Establecer la codificación para evitar caracteres chinos distorsionados $db_list = mysqli_query($conn, 'SHOW DATABASES'); while ($db = mysqli_fetch_object($db_list)) { echo $db->Database . "<br />"; } mysqli_close($conn); ?>

Obtener metadatos del servidor

Los siguientes comandos se pueden usar en el prompt de comandos de MySQL y también en scripts, como por ejemplo scripts PHP.

ComandoDescripción
SELECT VERSION( )Información de versión del servidor
SELECT DATABASE( )Nombre de la base de datos actual (o devuelve vacío)
SELECT USER( )Nombre de usuario actual
SHOW STATUSEstado del servidor
SHOW VARIABLESVariables de configuración del servidor
Otras extensiones