INFORMATION_SCHEMA获取元数据

谁借莪１个温暖的怀抱￠ 2022-04-23 09:30 184阅读 0赞

# 简介 #

MySQL默认会有存在一个库`information_schema`，这个库提供了对数据库元数据的访问，库中的表不是真正的表，磁盘上也没有对应的物理文件，里面的表实际上是视图。（是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。）

# INFORMATION\_SCHEMA库 #

### TABLES视图 ###

存放数据库中所有表的信息

# 利用元数据获取数据库状态的示例 #

### 统计mydb数据库下所有表的行数和数据量 ###

SELECT 
        table_schema,
        table_name,
        table_rows,
        (AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS size -- 平均行长度+索引长度就是每行占用的数据量
    FROM
        TABLES
    WHERE
        table_schema = 'mydb';

运行结果:

### 每个数据库下表的个数 ###

SELECT 
        TABLE_SCHEMA, COUNT(*)
    FROM
        TABLES
    GROUP BY TABLE_SCHEMA;

运行结果

mysql> SELECT 
        ->     TABLE_SCHEMA, COUNT(*)
        -> FROM
        ->     TABLES
        -> GROUP BY TABLE_SCHEMA;
    +--------------------+----------+
    | TABLE_SCHEMA       | COUNT(*) |
    +--------------------+----------+
    | crashcourse        |        6 |
    | cs                 |        2 |
    | information_schema |       61 |
    | mydb               |        7 |
    | mysql              |       31 |
    | performance_schema |       87 |
    | school             |        1 |
    | sys                |      101 |
    | test               |        1 |
    +--------------------+----------+
    9 rows in set (0.00 sec)

### 整个数据库中所有表的名字及所在的库名 ###

SELECT 
        table_name, table_schema
    FROM
        information_schema.TABLES;

运行结果太长，贴部分

### 查一下数据库引擎是`innodb`表，表名+库名 ###

SELECT 
        table_name, table_schema , engine
    FROM
        information_schema.TABLES
    WHERE
        ENGINE = 'Innodb';

运行结果

### 统计mydb数据库下Orders表的占用空间大小 ###

SELECT 
        table_name,
        (AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS data_mb
    FROM
        information_schema.TABLES
    WHERE
        TABLE_SCHEMA = 'mydb'
            AND TABLE_NAME = 'Orders';

运行结果

mysql> SELECT 
        ->     table_name,
        ->     (AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS data_mb
        -> FROM
        ->     information_schema.TABLES
        -> WHERE
        ->     TABLE_SCHEMA = 'mydb'
        ->         AND TABLE_NAME = 'Orders';
    +------------+------------+
    | table_name | data_mb    |
    +------------+------------+
    | Orders     | 0.01874924 |
    +------------+------------+
    1 row in set (0.00 sec)

### 统计整个数据库的数据量 ###

SELECT 
        SUM(AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS data_mb
    FROM
        information_schema.TABLES;

运行结果

mysql> SELECT 
        ->     SUM(AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS data_mb
        -> FROM
        ->     information_schema.TABLES;
    +------------+
    | data_mb    |
    +------------+
    | 0.58588886 |
    +------------+
    1 row in set, 6 warnings (0.04 sec)

### 统计mydb数据库的数据量 ###

SELECT 
        SUM(AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS data_mb
    FROM
        information_schema.TABLES
    WHERE
        TABLE_SCHEMA = 'mydb';

运行结果

mysql> SELECT 
        ->     SUM(AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS data_mb
        -> FROM
        ->     information_schema.TABLES
        -> WHERE
        ->     TABLE_SCHEMA = 'mydb';
    +------------+
    | data_mb    |
    +------------+
    | 0.10520554 |
    +------------+
    1 row in set, 12 warnings (0.00 sec)

### 备份mydb库下的所有表到/tmp目录下 ###

简单来说就是利用`CONCAT()`函数把备份语句拼接出来，存放到一个脚本文件中，然后一键执行就可以了

SELECT 
        CONCAT('mysqldump ',
                ' ',
                table_schema,
                ' ',
                table_name,
                '> /tmp/',
                table_schema,
                '_',
                table_name,
                '.sql') AS backup
    FROM
        information_schema.tables
    WHERE
        table_schema = 'mydb';

运行结果

mysql> SELECT 
        ->     CONCAT('mysqldump ',
        ->             ' ',
        ->             table_schema,
        ->             ' ',
        ->             table_name,
        ->             '> /tmp/',
        ->             table_schema,
        ->             '_',
        ->             table_name,
        ->             '.sql') as backup
        -> FROM
        ->     information_schema.tables
        -> WHERE
        ->     table_schema = 'mydb';
    +------------------------------------------------------------------+
    | backup                                                           |
    +------------------------------------------------------------------+
    | mysqldump    mydb Customers> /tmp/mydb_Customers.sql             |
    | mysqldump    mydb OrderItems> /tmp/mydb_OrderItems.sql           |
    | mysqldump    mydb Orders> /tmp/mydb_Orders.sql                   |
    | mysqldump    mydb Products> /tmp/mydb_Products.sql               |
    | mysqldump    mydb Productustomers> /tmp/mydb_Productustomers.sql |
    | mysqldump    mydb Vendors> /tmp/mydb_Vendors.sql                 |
    | mysqldump    mydb t1> /tmp/mydb_t1.sql                           |
    +------------------------------------------------------------------+
    7 rows in set (0.00 sec)

这样语句就生成了，接下来保存为文件就可以了，

运行发现会报错

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

需要给个权限，允许写入到别的目录，编辑配置文件`/etc/my.cnf`,添加下面一项

secure-file-priv=/tmp

重启数据库,重新执行就可以了

[root@localhost ~]# cat /tmp/backup.sh 
    mysqldump    mydb Customers> /tmp/mydb_Customers.sql
    mysqldump    mydb OrderItems> /tmp/mydb_OrderItems.sql
    mysqldump    mydb Orders> /tmp/mydb_Orders.sql
    mysqldump    mydb Products> /tmp/mydb_Products.sql
    mysqldump    mydb Productustomers> /tmp/mydb_Productustomers.sql
    mysqldump    mydb Vendors> /tmp/mydb_Vendors.sql
    mysqldump    mydb t1> /tmp/mydb_t1.sql

运行一下文件就可以备份了

[root@localhost ~]# sh /tmp/backup.sh 
    [root@localhost ~]# ls -l /tmp/my
    mydb_Customers.sql        mydb_Products.sql         mydb_Vendors.sql
    mydb_OrderItems.sql       mydb_Productustomers.sql  mysql.sock
    mydb_Orders.sql           mydb_t1.sql               mysql.sock.lock
    [root@localhost ~]# ls -l /tmp/mydb*
    -rw-r--r--. 1 root root 2688 Jan 17 15:22 /tmp/mydb_Customers.sql
    -rw-r--r--. 1 root root 2727 Jan 17 15:22 /tmp/mydb_OrderItems.sql
    -rw-r--r--. 1 root root 2245 Jan 17 15:22 /tmp/mydb_Orders.sql
    -rw-r--r--. 1 root root 2908 Jan 17 15:22 /tmp/mydb_Products.sql
    -rw-r--r--. 1 root root 2729 Jan 17 15:22 /tmp/mydb_Productustomers.sql
    -rw-r--r--. 1 root root 1740 Jan 17 15:22 /tmp/mydb_t1.sql
    -rw-r--r--. 1 root root 2505 Jan 17 15:22 /tmp/mydb_Vendors.sql

### 给mydb数据库下的每个表创建一个bak表 ###

SELECT 
        CONCAT('create table ',
                table_schema,
                '.',
                table_name,
                '_bak like ',
                table_schema,
                '.',
                table_name,
                ';')
    FROM
        information_schema.tables
    WHERE
        table_schema = 'mydb' INTO OUTFILE '/tmp/create.sql';

运行结果

mysql> SELECT 
        ->     CONCAT('create table ',
        ->             table_schema,
        ->             '.',
        ->             table_name,
        ->             '_bak like ',
        ->             table_schema,
        ->             '.',
        ->             table_name,
        ->             ';')
        -> FROM
        ->     information_schema.tables
        -> WHERE
        ->     table_schema = 'mydb' INTO OUTFILE '/tmp/create.sql';
    Query OK, 7 rows affected (0.00 sec)
    
    mysql> source /tmp/create.sql
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected (0.03 sec)
    
    Query OK, 0 rows affected (0.02 sec)
    
    Query OK, 0 rows affected (0.02 sec)
    --这个是我之前创建的一个视图，所以备份失败可以无视
    ERROR 1347 (HY000): 'mydb.Productustomers' is not BASE TABLE 
    
    Query OK, 0 rows affected (0.02 sec)
    
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> SHOW TABLES FROM mydb;
    +-----------------+
    | Tables_in_mydb  |
    +-----------------+
    | Customers       |
    | Customers_bak   |
    | OrderItems      |
    | OrderItems_bak  |
    | Orders          |
    | Orders_bak      |
    | Products        |
    | Products_bak    |
    | Productustomers |
    | Vendors         |
    | Vendors_bak     |
    | t1              |
    | t1_bak          |
    +-----------------+
    13 rows in set (0.00 sec)