MySQLSchema设计（四）一个MySQL里的JQuery：common_schema - 数据库编程

TABLE_NAME)

再以 _flattened_keys 为基表查看：

  FROM INFORMATION_SCHEMA.STATISTICS

作者Shlomi Noach便是认为"INFORMATION_SCHEMAprovides with complete info, it is ofter difficult to aggregate. It is sometimes too normalized, and at other times too de-normalized",他的诞生和Perl有些类似，系统管理员沃尔曾想用awk来完成，但其并不能满足他的需求，结果就是一门新的编程语言要诞生了。

mysql> select * from data_size_per_schema where table_schema='sakila'\G;
*************************** 1. row ***************************
      TABLE_SCHEMA: sakila
      count_tables: 16
       count_views: 7
  distinct_engines: 2
         data_size: 4297536
        index_size: 2581504
        total_size: 6879040
     largest_table: rental
largest_table_size: 2785280
1 row in set (0.16 sec)

DDL scripts
mysql> select table_name,sql_add_keys from sql_alter_table where table_schema='sakila'\G; *************************** 1. row *************************** table_name: actor sql_add_keys: ADD KEY `idx_actor_last_name`(`last_name`), ADD KEY `idx_actor_last_name_duplicate`(`last_name`), ADD PRIMARY KEY (`actor_id`) *************************** 2. row *************************** table_name: address sql_add_keys: ADD KEY `idx_fk_city_id`(`city_id`), ADD PRIMARY KEY (`address_id`) .................<此处省略输出>................. mysql> select * from sql_foreign_keys where table_schema='sakila'\G; *************************** 1. row *************************** TABLE_SCHEMA: sakila TABLE_NAME: address CONSTRAINT_NAME: fk_address_city drop_statement: ALTER TABLE `sakila`.`address` DROP FOREIGN KEY `fk_address_city` create_statement: ALTER TABLE `sakila`.`address` ADD CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `sakila`.`city` (`city_id`) ON DELETE RESTRICT ON UPDATE CASCADE ........................<此处省略输出>

.........................

mysql> select table_name,column_name,data_type,max_value,auto_increment value,auto_increment_ratio ratio -> from auto_increment_columns -> where table_schema='sakila'; +------------+--------------+-----------+------------+-------+--------+ | TABLE_NAME | COLUMN_NAME | DATA_TYPE | max_value | value | ratio | +------------+--------------+-----------+------------+-------+--------+ | actor | actor_id | smallint | 65535 | 201 | 0.0031 | | address | address_id | smallint | 65535 | 606 | 0.0092 | | category | category_id | tinyint | 255 | 17 | 0.0667 | | city | city_id | smallint | 65535 | 601 | 0.0092 | | country | country_id | smallint | 65535 | 110 | 0.0017 | | customer | customer_id | smallint | 65535 | 600 | 0.0092 | | film | film_id | smallint | 65535 | 1001 | 0.0153 | | inventory | inventory_id | mediumint | 16777215 | 4582 | 0.0003 | | language | language_id | tinyint | 255 | 7 | 0.0275 | | payment | payment_id | smallint | 65535 | 16050 | 0.2449 | | rental | rental_id | int | 2147483647 | 16050 | 0.0000 | | staff | staff_id | tinyint | 255 | 3 | 0.0118 | | store | store_id | tinyint | 255 | 3 | 0.0118 | +------------+--------------+-----------+------------+-------+--------+ 13 rows in set (0.90 sec)

mysql> select * from candidate_keys_recommended where table_schema='sakila'; +--------------+---------------+------------------------+--------------+------------+-----------------------+---------------------+ | table_schema | table_name | recommended_index_name | has_nullable | is_primary | count_column_in_index | column_names | +--------------+---------------+------------------------+--------------+------------+---------

MySQLSchema设计（四）一个MySQL里的JQuery：common_schema(二)