{"id":233280,"date":"2023-02-10T15:10:00","date_gmt":"2023-02-10T12:10:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=233280"},"modified":"2023-02-10T15:11:16","modified_gmt":"2023-02-10T12:11:16","slug":"proste-narzedzie-wiersza-polecen-php-do-konwersji-tabel-mysql-z-myisam-na-innodb-w-okreslonej-bazie-danych","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/pl\/proste-narzedzie-wiersza-polecen-php-do-konwersji-tabel-mysql-z-myisam-na-innodb-w-okreslonej-bazie-danych\/","title":{"rendered":"Proste narz\u0119dzie wiersza polece\u0144 PHP do konwersji tabel MySQL z MyISAM na InnoDB w okre\u015blonej bazie danych"},"content":{"rendered":"

Silnik bazy danych MySQL<\/a> MyISAM zosta\u0142 wycofany w najnowszej wersji od 5.5.1. MyISAM blokuje tabel\u0119, podczas gdy InnoDB (obecnie domy\u015blny silnik bazy danych dla MySQL od wersji 5.5.1) wykorzystuje ko\u0142ysanie na poziomie wiersza. InnoDB powinien by\u0107 preferowanym i zalecanym silnikiem bazy danych, je\u015bli chcesz tworzy\u0107 nowe aplikacje\/tabele. Jedn\u0105 z wad tabel MyISAM jest to, \u017ce nie obs\u0142uguj\u0105 transakcji. Zar\u00f3wno InnoDB, jak i MyISAM obs\u0142uguj\u0105 indeks wyszukiwania pe\u0142notekstowego.<\/p>\n

Mo\u017cesz u\u017cy\u0107 ALTER TABLE table ENGINE=INNODB, aby zmieni\u0107 tabel\u0119 na InnoDB, ale je\u015bli masz wiele tabel w bazie danych, np. WordPress, szukanie tabel MyISAM i konwersja jedna po drugiej b\u0119dzie \u017cmudne.<\/p>\n

Na szcz\u0119\u015bcie poni\u017csze PHP<\/a> mo\u017cna uruchomi\u0107 z wiersza polece\u0144 i pobiera parametry nazw baz danych \u2013 kt\u00f3re mo\u017cna przekaza\u0107 do wielu baz danych i b\u0119dzie szuka\u0107 tych tabel w tych bazach, kt\u00f3re s\u0105 aktualnie w silniku MyISAM i wyda\u0107 polecenie ALTER TABLE dla ka\u017cda z nich.<\/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
Przyk\u0142adowe u\u017cycie:<\/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
Istnieje kilka baz danych, kt\u00f3re nale\u017c\u0105 do MySQL np. sys, mysql \u2013 te tabele maj\u0105 silniki typu memory, csv \u2013 prosz\u0119 nie konwertowa\u0107 ich do InnoDB.<\/p>\n
MySQL<\/p>\n
Mo\u017cesz tak\u017ce polubi\u0107: Gad\u017cet PHP do konwersji tabel MyISAM w MySQL na InnoDB<\/a><\/p>\n
\u0179r\u00f3d\u0142o nagrywania:  helloacm.com<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"
Proste narz\u0119dzie wiersza polece\u0144 PHP do konwersji tabel MySQL z MyISAM na InnoDB w okre\u015blonej bazie danych<\/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":[897,721,721,919,897,919,1110,805,805,836,836,845,845,866,866],"tags":[1169],"class_list":{"0":"post-233280","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","6":"hentry","7":"category-kod","8":"category-deweloper","10":"category-inny","13":"category-n-a","14":"category-php-7","16":"category-przewodnik-dla-poczatkujacych","18":"category-samouczki","20":"category-wordpress-7","22":"tag-affiai-pl"},"_links":{"self":[{"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/posts\/233280","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/comments?post=233280"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/posts\/233280\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/media\/224526"}],"wp:attachment":[{"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/media?parent=233280"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/categories?post=233280"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/tags?post=233280"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}