mysql中怎么生成脚本_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)