mysql更改表数据的格式化_[原创]mysql_convert_table_format 批量修改表引擎

之前我用存储过程批量修改表的引擎。

昨天看手册，发现还有一工具可以在SHELL下直接修改。

很爽。

有关mysql_convert_table_format工具的详细介绍见

http://dev.mysql.com/doc/refman/5.1/en/mysql-convert-table-format.html

1、之前我写的批量修改表引擎的存储过程见

http://blog.chinaunix.net/u/29134/showart_454996.html

2、之前我写的拷贝数据库结构的存储过程见

http://blog.chinaunix.net/u/29134/showart_402376.html

3、简单脚本

#!/bin/sh

#

# Created by david yeung.

#

# To convert a table engine.

#

cd /usr/local/mysql/bin

echo 'Enter Host Name:'

read HOSTNAME

echo 'Enter User Name:'

read USERNAME

echo 'Enter Password:'

read PASSWD

echo 'Enter Socket Path:'

read SOCKETPATH

echo 'Enter Database Name:'

read DBNAME

echo 'Enter Table Name:'

read TBNAME

echo 'Enter Table Engine:'

read TBTYPE

./mysql_convert_table_format --host=$HOSTNAME --user=$USERNAME --password=$PASSWD --socket=$SOCKETPATH --type=$TBTYPE $DBNAME $TBNAME--verbose

4、详细执行结果如下。

mysql> show tables;

+------------------+

| Tables_in_t_girl |

+------------------+

| log              |

+------------------+

1 row in set (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| sakila             |

| t_girl             |

| test               |

+--------------------+

5 rows in set (0.00 sec)

拷贝sakila库的所有表结构到t_girl中。

mysql> call sp_copy_db_schema('sakila','t_girl');

Query OK, 0 rows affected (1.87 sec)

mysql> show tables;

+----------------------------+

| Tables_in_t_girl           |

+----------------------------+

| actor                      |

| actor_info                 |

| address                    |

| category                   |

| city                       |

| country                    |

| customer                   |

| customer_list              |

| film                       |

| film_actor                 |

| film_category              |

| film_list                  |

| film_text                  |

| inventory                  |

| language                   |

| log                        |

| nicer_but_slower_film_list |

| payment                    |

| rental                     |

| sales_by_film_category     |

| sales_by_store             |

| staff                      |

| staff_list                 |

| store                      |

+----------------------------+

24 rows in set (0.00 sec)

当前的引擎全是默认的MYISAM。

mysql> select concat(table_schema,'.',table_name) as table_name ,engine from information_schema.tables where table_schema = 't_girl';

+-----------------------------------+--------+

| table_name                        | engine |

+-----------------------------------+--------+

| t_girl.actor                      | MyISAM |

| t_girl.actor_info                 | MyISAM |

| t_girl.address                    | MyISAM |

| t_girl.category                   | MyISAM |

| t_girl.city                       | MyISAM |

| t_girl.country                    | MyISAM |

| t_girl.customer                   | MyISAM |

| t_girl.customer_list              | MyISAM |

| t_girl.film                       | MyISAM |

| t_girl.film_actor                 | MyISAM |

| t_girl.film_category              | MyISAM |

| t_girl.film_list                  | MyISAM |

| t_girl.film_text                  | MyISAM |

| t_girl.inventory                  | MyISAM |

| t_girl.language                   | MyISAM |

| t_girl.log                        | InnoDB |

| t_girl.nicer_but_slower_film_list | MyISAM |

| t_girl.payment                    | MyISAM |

| t_girl.rental                     | MyISAM |

| t_girl.sales_by_film_category     | MyISAM |

| t_girl.sales_by_store             | MyISAM |

| t_girl.staff                      | MyISAM |

| t_girl.staff_list                 | MyISAM |

| t_girl.store                      | MyISAM |

+-----------------------------------+--------+

24 rows in set (0.00 sec)

下来运行这个脚本。

[root@localhost ~]# ./convert_engines

Enter Host Name:

localhost

Enter User Name:

root

Enter Password:

1

Enter Socket Path:

/tmp/mysql_3310.sock

Enter Database Name:

t_girl

Enter Table Name:

如果想全部修改，就不用输入表名

Enter Table Engine:

innodb

Converting tables:

converting actor

converting actor_info

converting address

converting category

converting city

converting country

converting customer

converting customer_list

converting film

converting film_actor

converting film_category

converting film_list

converting film_text

converting inventory

converting language

log is already of type innodb;  Ignored

converting nicer_but_slower_film_list

converting payment

converting rental

converting sales_by_film_category

converting sales_by_store

converting staff

converting staff_list

converting store

现在查看结果

mysql> select concat(table_schema,'.',table_name) as table_name ,engine from information_schema.tables where table_schema = 't_girl';

+-----------------------------------+--------+

| table_name                        | engine |

+-----------------------------------+--------+

| t_girl.actor                      | InnoDB |

| t_girl.actor_info                 | InnoDB |

| t_girl.address                    | InnoDB |

| t_girl.category                   | InnoDB |

| t_girl.city                       | InnoDB |

| t_girl.country                    | InnoDB |

| t_girl.customer                   | InnoDB |

| t_girl.customer_list              | InnoDB |

| t_girl.film                       | InnoDB |

| t_girl.film_actor                 | InnoDB |

| t_girl.film_category              | InnoDB |

| t_girl.film_list                  | InnoDB |

| t_girl.film_text                  | InnoDB |

| t_girl.inventory                  | InnoDB |

| t_girl.language                   | InnoDB |

| t_girl.log                        | InnoDB |

| t_girl.nicer_but_slower_film_list | InnoDB |

| t_girl.payment                    | InnoDB |

| t_girl.rental                     | InnoDB |

| t_girl.sales_by_film_category     | InnoDB |

| t_girl.sales_by_store             | InnoDB |

| t_girl.staff                      | InnoDB |

| t_girl.staff_list                 | InnoDB |

| t_girl.store                      | InnoDB |

+-----------------------------------+--------+

比我之前写的那个存储过程爽多了吧。

阅读(7103) | 评论(1) | 转发(0) |