{"id":233276,"date":"2023-02-10T14:54:00","date_gmt":"2023-02-10T11:54:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=233276"},"modified":"2023-02-10T14:56:20","modified_gmt":"2023-02-10T11:56:20","slug":"yksinkertainen-php-komentorivityoekalu-muuntaa-mysql-taulukoita-myisamista-innodb-ksi-maeaeritetyssae-tietokannassa","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/fi\/yksinkertainen-php-komentorivityoekalu-muuntaa-mysql-taulukoita-myisamista-innodb-ksi-maeaeritetyssae-tietokannassa\/","title":{"rendered":"Yksinkertainen PHP-komentorivity\u00f6kalu muuntaa MySQL-taulukoita MyISAMista InnoDB: ksi m\u00e4\u00e4ritetyss\u00e4 tietokannassa"},"content":{"rendered":"

MySQL<\/a> MyISAM -tietokantamoottori on poistettu uusimmasta versiosta 5.5.1:n j\u00e4lkeen. MyISAM on taulukon lukitus, kun taas InnoDB (nyt MySQL:n oletustietokantamoottori versiosta 5.5.1 l\u00e4htien) k\u00e4ytt\u00e4\u00e4 rivitason keinumista. InnoDB:n tulisi olla ensisijainen ja suositeltu tietokantamoottori, jos haluat luoda uusia sovelluksia\/taulukoita. Yksi MyISAM-taulukoiden puute on, ett\u00e4 ne eiv\u00e4t tue tapahtumia. Sek\u00e4 InnoDB ett\u00e4 MyISAM tukevat koko tekstihakuindeksi\u00e4.<\/p>\n

Voit k\u00e4ytt\u00e4\u00e4 ALTER TABLE -taulukkoa ENGINE=INNODB muuttaaksesi taulukon InnoDB:ksi, mutta jos sinulla on useita taulukoita tietokannassa, esim. WordPress, on tyls\u00e4\u00e4 etsi\u00e4 MyISAM-taulukoita ja muuntaa yksitellen.<\/p>\n

Onneksi seuraava PHP<\/a> voidaan ajaa komentorivill\u00e4 ja se ottaa tietokantojen nimien parametrit \u2013 joita voit v\u00e4litt\u00e4\u00e4 monia tietokantoja, ja se etsii n\u00e4ist\u00e4 tietokannoista ne taulukot, jotka ovat t\u00e4ll\u00e4 hetkell\u00e4 MyISAM-moottorina ja antaa komennon ALTER TABLE jokainen heist\u00e4.<\/p>\n

<?php\n\/\/ <a class=\"vglnk\" target=\"_blank\" href=\"https:\/\/helloacm.com\/a-simple-php-command-line-tool-to-convert-mysql-tables-from-myisam-to-innodb-in-specified-database\/\" rel=\"nofollow\"><span>https<\/span><span>:\/\/<\/span><span>helloacm<\/span><span>.<\/span><span>com<\/span><span>\/<\/span><span>a<\/span><span>-<\/span><span>simple<\/span><span>-<\/span><span>php<\/span><span>-<\/span><span>command<\/span><span>-<\/span><span>line<\/span><span>-<\/span><span>tool<\/span><span>-<\/span><span>to<\/span><span>-<\/span><span>convert<\/span><span>-<\/span><span>mysql<\/span><span>-<\/span><span>tables<\/span><span>-<\/span><span>from<\/span><span>-<\/span><span>myisam<\/span><span>-<\/span><span>to<\/span><span>-<\/span><span>innodb<\/span><span>-<\/span><span>in<\/span><span>-<\/span><span>specified<\/span><span>-<\/span><span>database<\/span><span>\/<\/span><\/a>\nfunction ConvertAllTablesToInnoDB($database) {\n\u00a0 define(\"DB_HOST\", \"localhost\");\n\u00a0 define(\"DB_USER\", \"root\");\n\u00a0 define(\"DB_PASSWORD\", \"password\"); \u00a0\n\u00a0 $conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, $database);\n\u00a0 if (!$conn) {\n\u00a0 \u00a0 \u00a0 echo \"Error: Unable to connect to MySQL.\". PHP_EOL;\n\u00a0 \u00a0 \u00a0 echo \"Debugging errno: \". mysqli_connect_errno(). PHP_EOL;\n\u00a0 \u00a0 \u00a0 echo \"Debugging error: \". mysqli_connect_error(). PHP_EOL;\n\u00a0 \u00a0 \u00a0 exit; \u00a0 \u00a0 \u00a0 \u00a0\n\u00a0 } \u00a0 \u00a0 \u00a0\n\u00a0 $sql = \"SELECT TABLE_NAME FROM \n \u00a0 \u00a0 \u00a0INFORMATION_SCHEMA.TABLES\n \u00a0 \u00a0 \u00a0WHERE TABLE_SCHEMA = '$database' \n \u00a0 \u00a0 \u00a0AND ENGINE = 'MyISAM'\n \u00a0\";\n\u00a0 \n\u00a0 $rs = mysqli_query($conn, $sql);\n\u00a0 \n\u00a0 while ($row = mysqli_fetch_array($rs)) {\n\u00a0 \u00a0 \u00a0 $tbl = $row[0];\n\u00a0 \u00a0 \u00a0 $sql = \"ALTER TABLE `$tbl` ENGINE=INNODB\";\n\u00a0 \u00a0 \u00a0 echo $sql. \"n\";\n\u00a0 \u00a0 \u00a0 mysqli_query($conn, $sql);\n\u00a0 }\n}\n\u00a0 \u00a0 \nif ($argc < 2) {\n\u00a0 \u00a0 \u00a0 exit( \"Usage: php $argv[0] database1 database2 ...n\" );\n}\n\u00a0\nfor ($i = 1; $i < $argc; ++ $i) {\n\u00a0 \u00a0 \u00a0 echo \"ConvertAllTablesToInnoDB \". $argv[$i]. \"... n\";\n\u00a0 \u00a0 \u00a0 ConvertAllTablesToInnoDB($argv[$i]);\n}<\/code><\/pre>\n
K\u00e4ytt\u00f6esimerkki:<\/p>\n
# php convert_to_innodb.php wordpress\nConvertAllTablesToInnoDB wordpress... \nALTER TABLE `wp_commentmeta` ENGINE=INNODB\nALTER TABLE `wp_comments` ENGINE=INNODB\nALTER TABLE `wp_icl_string_translations` ENGINE=INNODB\nALTER TABLE `wp_imagify_files` ENGINE=INNODB\nALTER TABLE `wp_imagify_folders` ENGINE=INNODB\nALTER TABLE `wp_itsec_lockouts` ENGINE=INNODB\nALTER TABLE `wp_itsec_log` ENGINE=INNODB\nALTER TABLE `wp_itsec_temp` ENGINE=INNODB\nALTER TABLE `wp_links` ENGINE=INNODB\nALTER TABLE `wp_options` ENGINE=INNODB\nALTER TABLE `wp_postmeta` ENGINE=INNODB\nALTER TABLE `wp_posts` ENGINE=INNODB\nALTER TABLE `wp_term_relationships` ENGINE=INNODB\nALTER TABLE `wp_term_taxonomy` ENGINE=INNODB\nALTER TABLE `wp_termmeta` ENGINE=INNODB\nALTER TABLE `wp_terms` ENGINE=INNODB\nALTER TABLE `wp_usermeta` ENGINE=INNODB\nALTER TABLE `wp_users` ENGINE=INNODB\nALTER TABLE `wp_wc_download_log` ENGINE=INNODB\nALTER TABLE `wp_wc_webhooks` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_api_keys` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_attribute_taxonomies` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_downloadable_product_permissions` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_log` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_order_itemmeta` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_order_items` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_payment_tokenmeta` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_payment_tokens` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_sessions` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_shipping_zone_locations` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_shipping_zone_methods` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_shipping_zones` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_tax_rate_locations` ENGINE=INNODB\nALTER TABLE `wp_woocommerce_tax_rates` ENGINE=INNODB\nALTER TABLE `wp_wpio_images` ENGINE=INNODB\nALTER TABLE `wp_yith_ywpar_points_log` ENGINE=INNODB<\/code><\/pre>\n
Jotkut tietokannat kuuluvat MySQL:\u00e4\u00e4n, esim. sys, mysql \u2013 n\u00e4iss\u00e4 taulukoissa on moottorityyppej\u00e4, kuten muisti, csv \u2013 \u00e4l\u00e4 muunna niit\u00e4 InnoDB:ksi.<\/p>\n
MySQL<\/p>\n
Saatat my\u00f6s pit\u00e4\u00e4: PHP-gadget, joka muuntaa MySQL:n MyISAM-taulukot InnoDB:ksi<\/a><\/p>\n
:  helloacm.com<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"
Yksinkertainen PHP-komentorivity\u00f6kalu muuntaa MySQL-taulukoita MyISAMista InnoDB: ksi m\u00e4\u00e4ritetyss\u00e4 tietokannassa<\/p>\n","protected":false},"author":1,"featured_media":224526,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_wp_rev_ctl_limit":""},"categories":[895,719,719,895,917,917,1110,834,843,803,803,834,843,864,864],"tags":[1166],"class_list":{"0":"post-233276","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","6":"hentry","7":"category-koodi","8":"category-kehittaejae","11":"category-muut","13":"category-n-a","14":"category-opas-aloittelijoille","15":"category-opetusohjelmia","16":"category-php-5","20":"category-wordpress-5","22":"tag-affiai-fi"},"_links":{"self":[{"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/posts\/233276","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/comments?post=233276"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/posts\/233276\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/media\/224526"}],"wp:attachment":[{"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/media?parent=233276"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/categories?post=233276"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/tags?post=233276"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}