Mysql中如何批量生成脚本

[root@sql21 ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 3 to server version: 5.1.26-rc-log

Type 'help;' or '/h' for help. Type '/c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql>
mysql> use information_schema

Database changed

INFORMATION_SCHEMA提供了访问数据库元数据的方式。

元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”

最通俗我们可以看成是保存系统信息（数据字典）的scheme。

mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
28 rows in set (0.00 sec)

--生成批量脚本

mysql> SELECT concat("delete from  ",table_schema,".",table_name,";") FROM TABLES WHERE table_schema='mysql';
+---------------------------------------------------------+
| concat("delete from  ",table_schema,".",table_name,";") |
+---------------------------------------------------------+
| delete from  mysql.columns_priv;                        |
| delete from  mysql.db;                                  |
| delete from  mysql.event;                               |
| delete from  mysql.func;                                |
| delete from  mysql.general_log;                         |
| delete from  mysql.help_category;                       |
| delete from  mysql.help_keyword;                        |
| delete from  mysql.help_relation;                       |
| delete from  mysql.help_topic;                          |
| delete from  mysql.host;                                |
| delete from  mysql.ndb_binlog_index;                    |
| delete from  mysql.plugin;                              |
| delete from  mysql.proc;                                |
| delete from  mysql.procs_priv;                          |
| delete from  mysql.servers;                             |
| delete from  mysql.slow_log;                            |
| delete from  mysql.tables_priv;                         |
| delete from  mysql.time_zone;                           |
| delete from  mysql.time_zone_leap_second;               |
| delete from  mysql.time_zone_name;                      |
| delete from  mysql.time_zone_transition;                |
| delete from  mysql.time_zone_transition_type;           |
| delete from  mysql.user;                                |
+---------------------------------------------------------+
23 rows in set (0.01 sec)