首页 文章

如何将所有表从MyISAM转换为InnoDB?

提问于
浏览
221

我知道我可以单独发出一个alter table来将表存储从MyISAM更改为InnoDB .

我想知道是否有办法快速将所有这些更改为InnoDB?

26 回答

  • 2

    对此util脚本的一些修复

    SET @DATABASE_NAME = 'Integradb';
    
    SELECT  CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' ENGINE=InnoDB;') AS sql_statements
    FROM    information_schema.tables AS tb
    WHERE   table_schema = @DATABASE_NAME
    AND     `ENGINE` = 'MyISAM'
    AND     `TABLE_TYPE` = 'BASE TABLE'
    ORDER BY table_name DESC;
    
  • 1
    <?php
        // connect your database here first 
        // 
    
        // Actual code starts here 
    
        $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA = 'your_database_name' 
            AND ENGINE = 'MyISAM'";
    
        $rs = mysql_query($sql);
    
        while($row = mysql_fetch_array($rs))
        {
            $tbl = $row[0];
            $sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
            mysql_query($sql);
        }
    ?>
    
  • 16

    运行此SQL语句(在MySQL客户端,phpMyAdmin或其中),以检索数据库中的所有MyISAM表 .

    用您的数据库名称替换 name_of_your_db 变量的值 .

    SET @DATABASE_NAME = 'name_of_your_db';
    
    SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
    FROM    information_schema.tables AS tb
    WHERE   table_schema = @DATABASE_NAME
    AND     `ENGINE` = 'MyISAM'
    AND     `TABLE_TYPE` = 'BASE TABLE'
    ORDER BY table_name DESC;
    

    然后,复制输出并作为新的SQL查询运行 .

  • 146
    SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') 
    FROM INFORMATION_SCHEMA.TABLES
    WHERE ENGINE='MyISAM'
    AND table_schema = 'mydatabase';
    

    奇迹般有效 .

    这将为您提供可以批量运行的alter query的所有表的列表

  • 20

    在下面的脚本中,将<username>,<password>和<schema>替换为您的特定数据 .

    要显示可以复制粘贴到mysql客户端会话的语句,请键入以下内容:

    echo 'SHOW TABLES;' \
     | mysql -u <username> --password=<password> -D <schema> \
     | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
     | column -t \
    

    要简单地执行更改,请使用以下命令:

    echo 'SHOW TABLES;' \
     | mysql -u <username> --password=<password> -D <schema> \
     | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
     | column -t \
     | mysql -u <username> --password=<password> -D <schema>
    

    信用:这是this article中概述的变体 .

  • 2

    一条线:

    mysql -u root -p dbName -e 
     "show table status where Engine='MyISAM';" | awk 
     'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}'  | 
      mysql -u root -p dbName
    
  • 1

    在phpMyAdmin中将其用作sql查询

    SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;') 
    FROM information_schema.tables 
    WHERE engine = 'MyISAM';
    
  • 1

    您可以在mysql命令行工具中执行此语句:

    echo "SELECT concat('ALTER TABLE `',TABLE_NAME,'` ENGINE=InnoDB;')
    FROM Information_schema.TABLES 
    WHERE ENGINE != 'InnoDB' AND TABLE_TYPE='BASE TABLE' 
    AND TABLE_SCHEMA='name-of-database'" | mysql > convert.sql
    

    您可能需要使用以下命令指定用户名和密码:mysql -u username -p结果是一个sql脚本,您可以将其重新导入mysql:

    mysql name-of-database < convert.sql
    

    在上面的语句和命令行中替换“name-of-database” .

  • 8

    它还没有被提及,所以我会为后人写它:

    如果您转储并重新加载您的dta),您只需修改 mysqldump 的输出:

    mysqldump --no-data DBNAME | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > my_schema.sql;
    mysqldump --no-create-info DBNAME > my_data.sql;
    

    然后再次加载:

    mysql DBNAME < my_schema.sql && mysql DBNAME < my_data.sql
    

    (另外,在我有限的经验中,这可能比更改表'live'要快得多 . 它可能取决于数据和索引的类型 . )

  • 7

    这很简单只有两步就是复制和粘贴:

    步骤1 .

    SET @DATABASE_NAME = 'name_of_your_db';
      SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS  sql_statements FROM information_schema.tables AS tb WHERE   table_schema = @DATABASE_NAME AND `ENGINE` = 'MyISAM' AND `TABLE_TYPE` = 'BASE TABLE' ORDER BY table_name DESC;
    

    (将所有结果复制并粘贴到sql选项卡中)

    步骤2 :(将所有结果复制到sql选项卡中)并粘贴到该行的下方

    开始交易;

    承诺;

    例如 . 开始交易;

    ALTER TABLE admin_files ENGINE = InnoDB;

    承诺;

  • 487

    这是Django用户的一种方法:

    from django.core.management.base import BaseCommand
    from django.db import connections
    
    
    class Command(BaseCommand):
    
        def handle(self, database="default", *args, **options):
    
            cursor = connections[database].cursor()
    
            cursor.execute("SHOW TABLE STATUS");
    
            for row in cursor.fetchall():
                if row[1] != "InnoDB":
                    print "Converting %s" % row[0],
                    result = cursor.execute("ALTER TABLE %s ENGINE=INNODB" % row[0])
                    print result
    

    在文件夹管理/命令下将其添加到您的应用程序/然后您可以使用manage.py命令转换所有表:

    python manage.py convert_to_innodb
    
  • 0

    从mysql内部,您可以使用文本编辑器进行搜索/替换:

    SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';
    

    注意:您应该忽略information_schema和mysql,因为"The mysql and information_schema databases, that implement some of the MySQL internals, still use MyISAM. In particular, you cannot switch the grant tables to use InnoDB."(http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html

    在任何情况下,请注意要忽略并运行的表:

    SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';
    

    现在只需将该列表复制/粘贴到文本编辑器中并搜索/替换“|”用“ALTER TABLE”等

    然后你会有一个这样的列表,你可以简单地粘贴到你的mysql终端:

    ALTER TABLE arth_commentmeta           ENGINE=Innodb;
    ALTER TABLE arth_comments              ENGINE=Innodb;
    ALTER TABLE arth_links                 ENGINE=Innodb;
    ALTER TABLE arth_options               ENGINE=Innodb;
    ALTER TABLE arth_postmeta              ENGINE=Innodb;
    ALTER TABLE arth_posts                 ENGINE=Innodb;
    ALTER TABLE arth_term_relationships    ENGINE=Innodb;
    ALTER TABLE arth_term_taxonomy         ENGINE=Innodb;
    ALTER TABLE arth_terms                 ENGINE=Innodb;
    ALTER TABLE arth_usermeta              ENGINE=Innodb;
    

    如果你的文本编辑器不能轻易做到这一点,这里是另一个解决方案,从linux终端获取一个类似的列表(你可以粘贴到mysql)只为数据库的一个前缀:

    mysql -u [username] -p[password] -B -N -e 'show tables like "arth_%"' [database name] | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;"
    
  • 4

    要为所有非系统模式中的所有表生成ALTER语句,请按这些模式/表排序,运行以下命令:

    SELECT  CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', table_name, ' ENGINE=InnoDB;') AS sql_statements
    FROM    information_schema.tables
    WHERE   TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'innodb', 'sys', 'tmp')
    AND     `ENGINE` = 'MyISAM'
    AND     `TABLE_TYPE` = 'BASE TABLE'
    ORDER BY TABLE_SCHEMA, table_name DESC;
    

    之后,通过客户端运行这些查询以执行更改 .

    • 答案基于以上答案,但改进了架构处理 .
  • 2

    一个普通的MySQL版本 .

    您可以简单地启动mysql可执行文件,使用数据库并复制粘贴查询 .

    这会将当前数据库中的所有MyISAM表转换为INNODB表 .

    DROP PROCEDURE IF EXISTS convertToInnodb;
    DELIMITER //
    CREATE PROCEDURE convertToInnodb()
    BEGIN
    mainloop: LOOP
      SELECT TABLE_NAME INTO @convertTable FROM information_schema.TABLES
      WHERE `TABLE_SCHEMA` LIKE DATABASE()
      AND `ENGINE` LIKE 'MyISAM' ORDER BY TABLE_NAME LIMIT 1;
      IF @convertTable IS NULL THEN 
        LEAVE mainloop;
      END IF;
      SET @sqltext := CONCAT('ALTER TABLE `', DATABASE(), '`.`', @convertTable, '` ENGINE = INNODB');
      PREPARE convertTables FROM @sqltext;
      EXECUTE convertTables;
      DEALLOCATE PREPARE convertTables;
      SET @convertTable = NULL;
    END LOOP mainloop;
    
    END//
    DELIMITER ;
    
    CALL convertToInnodb();
    DROP PROCEDURE IF EXISTS convertToInnodb;
    
  • 48

    你可以编写一个脚本来用你最喜欢的脚本语言来完成它 . 该脚本将执行以下操作:

    • 问题 SHOW FULL TABLES .

    • 对于返回的每一行,检查第二列是否为 'BASE TABLE' 而不是 'VIEW' .

    • 如果不是 'VIEW' ,请发出相应的 ALTER TABLE 命令 .

  • 19

    试试这个shell脚本

    DBENGINE='InnoDB' ;
    DBUSER='your_db_user' ;
    DBNAME='your_db_name' ;
    DBHOST='your_db_host'
    DBPASS='your_db_pass' ;
    mysqldump --add-drop-table -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME > mtest.sql; mysql -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME -Nse "SHOW TABLES;" | while read TABLE ; do mysql -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME -Nse "ALTER TABLE $TABLE ENGINE=$DBENGINE;" ; done
    
  • 5

    我是一个新手,不得不找到我自己的解决方案,因为网络上的mysql命令通常充斥着拼写错误,为刚开始的人们创造了真实的生活噩梦 . 这是我的解决方案......

    我没有使用每个表中的1个命令,而是使用excel一次准备了许多命令(准备复制和粘贴) .

    怎么样?展开你的putty窗口并输入mysql,然后运行命令“SHOW TABLE STATUS;”并将输出复制/粘贴到microsoft excel . 转到“数据”选项卡并使用“文本到列”功能,用空格键分隔列 . 然后按列显示您的表类型对列进行排序,并删除表已经是InnoDb格式的所有行(因为我们不需要运行针对他们的命令,他们已经完成了) . 然后在tables列的左侧添加2列,在右侧添加2列 . 然后在第1列中粘贴命令的第一部分(见下文) . 第2列应仅包含空格 . 第3列是您的表列 . 第4列应仅包含空格 . 第5列是命令的最后一部分 . 它应该如下所示:

    column-1        column-2            column-3         column-4     column-5
    ALTER TABLE     t_lade_tr           ENGINE=InnoDB;
    ALTER TABLE     t_foro_detail_ms    ENGINE=InnoDB;
    ALTER TABLE     t_ljk_ms            ENGINE=InnoDB;
    

    然后一次将大约5行复制并粘贴到mysql中 . 这将一次转换大约5 . 我注意到如果我做的不止于此,那么命令就会失败 .

  • 2

    在我的例子中,我是从默认为MyISAM的MySQL实例迁移到具有默认的InnoDB的MariaDB实例 .

    Per MariaDB Migration Doc's.

    在旧服务器上运行:

    mysqldump -u root -p --skip-create-options --all-databases > migration.sql
    

    --skip-create-options确保数据库服务器在加载数据时使用默认存储引擎,而不是MyISAM .

    mysql -u root -p < migration.sql
    

    这引发了关于创建mysql.db的错误,但现在一切都很好:)

  • 0
    <?php
    
      // connect your database here first
    
      mysql_connect('host', 'user', 'pass');
    
      $databases = mysql_query('SHOW databases');
    
      while($db = mysql_fetch_array($databases)) {
        echo "database => {$db[0]}\n";
        mysql_select_db($db[0]);
    
        $tables = mysql_query('SHOW tables');
    
        while($tbl = mysql_fetch_array($tables)) {
          echo "table => {$tbl[0]}\n";
          mysql_query("ALTER TABLE {$tbl[0]} ENGINE=MyISAM");
        }
      }
    
  • -1

    使用此行来更改单个表的数据库引擎 .

    ALTER TABLE table_name ENGINE = INNODB;
    
  • 1

    这是一个简单的PHP脚本 .

    <?php
        @error_reporting(E_ALL | E_STRICT);
        @ini_set('display_errors', '1');
    
    
        $con = mysql_connect('server', 'user', 'pass');
        $dbName = 'moodle2014';
    
        $sql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '".$dbName."';";
        $rs = mysql_query($sql, $con);
    
        $count = 0;
        $ok = 0;
        while($row = mysql_fetch_array($rs)){
                $count ++;
                $tbl = $row[0];
                $sql = "ALTER TABLE ".$dbName.".".$tbl." ENGINE=INNODB;";
                $resultado = mysql_query($sql);
                if ($resultado){
                        $ok ++;
                        echo $sql."<hr/>";
                }
        }
        if ($count == $ok){
                echo '<div style="color: green"><b>ALL OK</b></div>';
        }else{
                echo '<div style="color: red"><b>ERRORS</b>Total tables: '.$count.', updated tables:'.$ok.'</div>';
        }
    
  • 5
    <?php
    
    // Convert all MyISAM tables to INNODB tables in all non-special databases.
    // Note: With MySQL less than 5.6, tables with a fulltext search index cannot be converted to INNODB and will be skipped.
    
    if($argc < 4)
        exit("Usage: {$argv[0]} <host> <username> <password>\n");
    $host = $argv[1];
    $username = $argv[2];
    $password = $argv[3];
    
    // Connect to the database.
    if(!mysql_connect($host, $username, $password))
        exit("Error opening database. " . mysql_error() . "\n");
    
    // Get all databases except special ones that shouldn't be converted.
    $databases = mysql_query("SHOW databases WHERE `Database` NOT IN ('mysql', 'information_schema', 'performance_schema')");
    if($databases === false)
        exit("Error showing databases. " . mysql_error() . "\n");
    
    while($db = mysql_fetch_array($databases))
    {
        // Select the database.
        if(!mysql_select_db($db[0]))
            exit("Error selecting database: {$db[0]}. " . mysql_error() . "\n");
        printf("Database: %s\n", $db[0]);
    
        // Get all MyISAM tables in the database.
        $tables = mysql_query("SHOW table status WHERE Engine = 'MyISAM'");
        if($tables === false)
            exit("Error showing tables. " . mysql_error() . "\n");
    
        while($tbl = mysql_fetch_array($tables))
        {
            // Convert the table to INNODB.
            printf("--- Converting %s\n", $tbl[0]);
            if(mysql_query("ALTER TABLE `{$tbl[0]}` ENGINE = INNODB") === false)
                printf("--- --- Error altering table: {$tbl[0]}. " . mysql_error() . "\n");
        }
    }
    
    mysql_close();
    
    ?>
    
  • 8

    刚刚测试了另一种(简单?)方式,并为我工作 .

    只需将您的数据库导出为.sql文件,使用gedit或notepad编辑它;

    ENGINE=MyISAM 替换为 ENGINE=INNODB 并保存已编辑的文件

    完成的数字或替换应该是您的表的数量

    将其导入MySQL(phpMyAdmin或命令行)

    还有瞧!

  • 1

    还有另一个选择......以下是如何在ansible中做到这一点 . 它假定您的数据库名称在 dbname 中,并且您已经配置了访问权限 .

    - name: Get list of DB tables that need converting to InnoDB
      command: >
        mysql --batch --skip-column-names --execute="SELECT TABLE_NAME
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = '{{ dbname }}' AND ENGINE = 'MyISAM';"
      register: converttables
      check_mode: no
      changed_when: False
    
    - name: Convert any unconverted tables
      command: >
        mysql --batch --skip-column-names --execute="ALTER TABLE `{{ dbname }}`.`{{ item }}` ENGINE = InnoDB;"
      with_items: "{{ converttables.stdout_lines }}"
    
  • 2

    用于mysqli连接;

    <?php
    
    $host       = "host";
    $user       = "user";
    $pass       = "pss";
    $database   = "db_name";
    
    
    $connect = new mysqli($host, $user, $pass, $database);  
    
    // Actual code starts here Dont forget to change db_name !!
    $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'db_name' 
        AND ENGINE = 'MyISAM'";
    
    $rs = $connect->query($sql);
    
    while($row = $rs->fetch_array())
    {
        $tbl = $row[0];
        $sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
        $connect->query($sql);
    } ?>
    
  • 23

    cd / var / lib / mysql / DBNAME

    ls | grep“.frm”| cut -d“ . ” -f1 | xargs -I {} -n1 mysql -D DBNAME -e“alter table {} ENGINE = INNODB;” -uroot -pXXXXX

相关问题