Skip to main content

How to convert MySQL collation from utf8mb4 to utf8

Recently I faced a tough situation with one of my WordPress site at the time of exporting MySQL database. My development MySQL server supports utf8mb4 collation but the production MySQL server does not support that. So, I had to convert all the tables of the MySQL database from  utf8mb4 to utf8.  The actual task was conversion from utf8mb4_unicode_ci to utf8_general_ci

I tried to convert manually several times but it did not work. My question was what is the difference between utf8mb4 and utf8?

utf8mb4 (as well as standard UTF-8) can directly store any character specified by Unicode; the former is fixed size at 4 bytes per character whereas the latter is between 1 and 4 bytes per character.

utf8 can only store the first 65,536 codepoints, which will cover CJVK (Chinese, Japanese, Vietnam, Korean), and use 1 to 3 bytes per character.

So, the real process will be ALTER the database as well as each database table to change the CHARACTER SET.  Using a PHP script I made the conversion and it worked perfectly. This is the PHP script which will convert your MySQL database collation from utf8mb4_unicode_ci to utf8_general_ci instantly. 

  1. Copy the below PHP script and paste in a file say, ‘dbconversion.php’.
  2. Now put this file in your server (development/production).
  3. Run this script from ‘yourdomain.com/dbconversion.php’.
  4. It’s all. It will convert everything and you will get a success message.

Note: If you are using WordPress website and you need to convert your database any time, make sure ‘utf8’ Charset will be defined in your ‘wp-config.php’ file as
define(‘DB_CHARSET’, ‘utf8’);

https://gist.github.com/sanjaybhowmick/57b2cdf96d952d207a5d

Comments

Popular posts from this blog

How to choose the right content management system for your website

Creating a website can be an exciting process, but it can also be overwhelming, especially when it comes to choosing the right Content Management System (CMS). A CMS is an essential component of any website, as it allows you to manage and publish your digital content. With so many CMS options available, it can be challenging to decide which one is the right fit for your website. In this article, we will guide you through the process of selecting the perfect CMS that fits your website’s needs, with real examples to help you make an informed decision. Determine Your Website’s Purpose The first step in choosing the right CMS for your website is to determine your website’s purpose. Are you creating a blog, an e-commerce site, or a portfolio website? Different CMS platforms cater to different website types, and choosing the right one will ensure that you have access to the necessary tools and features. For example, if you’re building an e-commerce site, you may want to

A comprehensive guide for best practices and tools to build responsive websites

Building Responsive Websites: Best Practices and Tools In the fast-paced digital world, having a responsive website has become a necessity. With the increasing use of mobile devices and varying screen sizes, it’s crucial to ensure your website looks and functions flawlessly across all platforms. In this comprehensive guide, we’ll explore the best practices and essential tools for building responsive websites that deliver optimal user experiences. Why Responsive Design Matters in Today’s Digital Landscape In today’s mobile-centric era, users expect websites to adapt seamlessly to their devices, whether they’re browsing on a desktop, tablet, or smartphone. Responsive design is the key to meeting these expectations. It allows your website to automatically adjust its layout, images, and content based on the screen size and orientation of the device. By implementing responsive design, you provide a consistent and user-friendly experience, regardless of how users acces

Important WordPress theme functions every developer needs

When we develop any custom WordPress theme we need to use different functions. We need to write those functions in functions.php file of the custom theme. I have prepared some of those basic functions and included in the functions.php file. I have mentioned the usage / procedure to use the function in the same file. You have to download the functions.php file and put it inside your theme folder. Following functions are included in functions.php file. Navigation / Menu Remove default UL markup from wp_nav_menu output Enable featured image with custom dimension Image compression while uploading Remove width and height attributes from inserted images Enable sidebar widget Shorten post / page title by word limit Shorten excerpt by word limit Shorten content by word limit Strip images from content Display the content of a page by page ID Display the content of a page by page slug Anti spam email shortcode inside content editor Change default sender email address for generat