{"id":1877,"date":"2019-10-03T16:09:12","date_gmt":"2019-10-03T08:09:12","guid":{"rendered":"https:\/\/www.mondoze.com\/guide\/?post_type=kb&#038;p=1877"},"modified":"2022-10-07T07:51:23","modified_gmt":"2022-10-06T23:51:23","slug":"how-to-change-all-mysql-database-table-collation","status":"publish","type":"kb","link":"https:\/\/www.mondoze.com\/guide\/kb\/how-to-change-all-mysql-database-table-collation","title":{"rendered":"How to change all MySQL database table collation"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-post\" data-elementor-id=\"1877\" class=\"elementor elementor-1877\" data-elementor-settings=\"[]\">\n\t\t\t\t\t\t<div class=\"elementor-inner\">\n\t\t\t\t\t\t\t<div class=\"elementor-section-wrap\">\n\t\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-2eb2854c elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"2eb2854c\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t\t\t<div class=\"elementor-row\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-3a75dcc2\" data-id=\"3a75dcc2\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-column-wrap elementor-element-populated\">\n\t\t\t\t\t\t\t<div class=\"elementor-widget-wrap\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-9501d27 elementor-widget elementor-widget-heading\" data-id=\"9501d27\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">How To Change All MySQL Database Table Collation<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-299e8b3e elementor-widget elementor-widget-text-editor\" data-id=\"299e8b3e\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t<div class=\"elementor-text-editor elementor-clearfix\">\n\t\t\t\t<p>After change the collation of a database, only the new tables will be created with the new collation. To change all the database collation, you can use the php script as below as it can change all the collation for all the existing table.\u00a0<\/p><p><!--?php &lt;br ?--> $db = mysql_connect(&#8216;localhost&#8217;,&#8217;<strong>database_user<\/strong>&#8216;,&#8217;<strong>Password<\/strong>&#8216;);<br \/>if(!$db) echo &#8220;Cannot connect to the database &#8211; incorrect details&#8221;;<br \/>mysql_select_db(&#8216;<strong>database_name<\/strong>&#8216;); $result=mysql_query(&#8216;show tables&#8217;);<br \/>while($tables = mysql_fetch_array($result)) {<br \/>foreach ($tables as $key =&gt; $value) {mysql_query(&#8220;ALTER TABLE $value COLLATE\u00a0<strong>Collation<\/strong>&#8220;);<br \/>}}<br \/>echo &#8220;The collation of your database has been successfully changed!&#8221;;<br \/>?&gt;<\/p><p><i><b>Note:\u00a0<\/b><\/i><br \/><strong>database_user<\/strong>\u00a0: Database Username<br \/><strong>Password<\/strong>: Database user password<br \/><strong>database_name<\/strong>: Database Name<br \/><strong>Collation<\/strong>\u00a0: new collation<\/p>\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>How To Change All MySQL Database Table Collation After change the collation of a database, only the new tables will be created with the new collation. To change all the database collation, you can use the php script as below as it can change all the collation for all the existing table.\u00a0 $db = mysql_connect(&#8216;localhost&#8217;,&#8217;database_user&#8216;,&#8217;Password&#8216;);if(!$db) &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/www.mondoze.com\/guide\/kb\/how-to-change-all-mysql-database-table-collation\"> <span class=\"screen-reader-text\">How to change all MySQL database table collation<\/span> Read More \u00bb<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"default","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}}},"kbtopic":[123],"kbtag":[110],"mkb_version":[],"_links":{"self":[{"href":"https:\/\/www.mondoze.com\/guide\/wp-json\/wp\/v2\/kb\/1877"}],"collection":[{"href":"https:\/\/www.mondoze.com\/guide\/wp-json\/wp\/v2\/kb"}],"about":[{"href":"https:\/\/www.mondoze.com\/guide\/wp-json\/wp\/v2\/types\/kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.mondoze.com\/guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mondoze.com\/guide\/wp-json\/wp\/v2\/comments?post=1877"}],"version-history":[{"count":6,"href":"https:\/\/www.mondoze.com\/guide\/wp-json\/wp\/v2\/kb\/1877\/revisions"}],"predecessor-version":[{"id":18572,"href":"https:\/\/www.mondoze.com\/guide\/wp-json\/wp\/v2\/kb\/1877\/revisions\/18572"}],"wp:attachment":[{"href":"https:\/\/www.mondoze.com\/guide\/wp-json\/wp\/v2\/media?parent=1877"}],"wp:term":[{"taxonomy":"kbtopic","embeddable":true,"href":"https:\/\/www.mondoze.com\/guide\/wp-json\/wp\/v2\/kbtopic?post=1877"},{"taxonomy":"kbtag","embeddable":true,"href":"https:\/\/www.mondoze.com\/guide\/wp-json\/wp\/v2\/kbtag?post=1877"},{"taxonomy":"mkb_version","embeddable":true,"href":"https:\/\/www.mondoze.com\/guide\/wp-json\/wp\/v2\/mkb_version?post=1877"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}