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)