快乐学习
前程无忧、中华英才非你莫属!

mysql_convert_table_format 批量修改表引擎

之前我用存储过程批量修改表的引擎。
昨天看手册,发现还有一工具可以在SHELL下直接修改。
很爽。
有关mysql_convert_table_format工具的详细介绍见
[url]http://dev.mysql.com/doc/refman/5.1/en/mysql-convert-table-format.html[/url]
1、之前我写的批量修改表引擎的存储过程见
[url]http://blog.chinaunix.net/u/29134/showart_454996.html[/url]
2、之前我写的拷贝数据库结构的存储过程见
[url]http://blog.chinaunix.net/u/29134/showart_402376.html[/url]
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 |

+———————————–+——–+
打赏

未经允许不得转载:同乐学堂 » mysql_convert_table_format 批量修改表引擎

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!