How to convert an entire MySQL database when text was Not correctly stored, and stored in some other charset

SOURCE CODE OF SQL FILE IS:

    -- phpMyAdmin SQL Dump
-- version 4.0.4.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Oct 20, 2021 at 07:03 PM
-- Server version: 5.5.32
-- PHP Version: 5.4.16

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="7c333038233f343d2e3d3f28392e232f3928233f3035393228413c">[email protected]</a>@CHARACTER_SET_CLIENT */;
/*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="9ed1d2dac1ddd6dfccdfddcadbccc1cddbcac1ccdbcdcbd2cacda3de">[email protected]</a>@CHARACTER_SET_RESULTS */;
/*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="1a55565e45595556565b4e53555445595554545f594e535554275a">[email protected]</a>@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `test_table`
--

CREATE TABLE IF NOT EXISTS `test_table` (
  `id` int(11) NOT NULL,
  `title` text CHARACTER SET utf8
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `test_table`
--

INSERT INTO `test_table` (`id`, `title`) VALUES
(2, 'ÛŒØ§Ø´ÛŒÙ„ Ù‚Ø·Ø±Ù‡'),
(3, 'ØªÙˆØ³Ø¹Ù‡ ØµÙ†Ø¹Øª ÙØ±Ø¢ÛŒÙ†Ø¯ Ø¨Ø§Ø®ØªØ±'),
(4, 'Ø§Ù„ÙˆÙ†Ø¯ Ù…Ú©Ø´'),
(5, 'Ù†Ù‡Ø§Ø¯Ù‡ Ú¯Ø³ØªØ±'),
(6, 'Ù¾Ø§Ú©Ø§Ù† Ø¨Ø°Ø±');

/*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="e5a6ada4b7a4a6b1a0b7bab6a0b1baa6a9aca0abb1d8a5aaa9a1">[email protected]</a>_CHARACTER_SET_CLIENT */;
/*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="95d6ddd4c7d4d6c1d0c7cac6d0c1cac7d0c6c0d9c1c6a8d5dad9d1">[email protected]</a>_CHARACTER_SET_RESULTS */;
/*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6724282b2b26332e282938242829292224332e28295a27282b23">[email protected]</a>_COLLATION_CONNECTION */;

link to :

![sample query for this problem][2]

**What is the problem: **

By using of

ALTER DATABASE

and

ALTER TABLE

commands.
same as

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

we can convert an entire MySQL database characterset and collation to UTF-8
when text was Not correctly stored
but my problem is that: ALTER TABLE tablename CHARACTER SET utf8
only sets the default charset on a table which is used for newly created columns.
And it does not convert existing columns that already have a char set.
how do it only by MySQL Commands and without exporting to file

pay attention:
This problem started when the character settings (character set ) in the server settings were not set correctly to utf8 and remained unchanged on latin1, which is mysql by default setting.

Edit: add source sql insted linking to it

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Well-Defined problems lead to breakthrough solutions

Check out this simple solution:

UPDATE `test_table` SET `title` = CONVERT(CAST(CONVERT(title USING latin1) AS 
BINARY)  USING utf8)

Left: Run code

Right: Result of code

Thanks,
IVO GELOV

He is right

The magic was inside CONVERT TO

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating