CODEWITHSUNDEEP
mysqlphpmyadmin
Acidburns
Acidburns

Reputation: 143

MySQL convert column from Latin1 to UTF8

My old database backup used to have UTF-8 chars into some columns, but i suppose it was exported to latin1 charset.

Now i have problem with some columns that have "Cyrillic (utf8) characters/text" in it: http://prntscr.com/vu4uql

I was able to convert few columns to UTF8 , using this command:

UPDATE jos_content SET title = CONVERT(CAST(CONVERT(title USING latin1) AS BINARY) USING utf8);

Columns: "title", "introtext" are now converted and showing "Cyrillic (utf8) characters/text"

But i have problem only with one column "fulltext" for which when i try to use the above command in SQL > phpmyadmin , i receive the following error:

Error
Static analysis:

18 errors were found during analysis.

Unrecognized keyword. (near "fulltext" at position 23)
Unexpected token. (near "=" at position 32)
Unrecognized keyword. (near "CONVERT" at position 34)
Unexpected token. (near "(" at position 41)
Unrecognized keyword. (near "CAST" at position 42)
Unexpected token. (near "(" at position 46)
Unrecognized keyword. (near "CONVERT" at position 47)
Unexpected token. (near "(" at position 54)
Unrecognized keyword. (near "fulltext" at position 55)
Unrecognized keyword. (near "USING" at position 64)
Unexpected token. (near "latin1" at position 70)
Unexpected token. (near ")" at position 76)
Unrecognized keyword. (near "AS" at position 78)
Unrecognized keyword. (near "BINARY" at position 81)
Unexpected token. (near ")" at position 87)
Unrecognized keyword. (near "USING" at position 89)
Unexpected token. (near "utf8" at position 95)
Unexpected token. (near ")" at position 99)
SQL query:

UPDATE jos_content SET fulltext = CONVERT(CAST(CONVERT(fulltext USING latin1) AS BINARY) USING utf8)

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'fulltext = CONVERT(CAST(CONVERT(fulltext USING latin1) AS BINARY) USING utf8)' at line 1

How can i fix/convert this column as previous one ?

Upvotes: 0

Views: 216

Answers (1)

Isaac Bennetch
Isaac Bennetch

Reputation: 12412

Based on what you've posted, the problem is that you're using fulltext as a column name, but it's also a reserved word used for defining an index type when creating or modifying a table structure.

Surround each occurrence of the word fulltext with backticks, like this:

UPDATE jos_content SET `fulltext` = CONVERT(CAST(CONVERT(`fulltext` USING latin1) AS BINARY) USING utf8)

It's a somewhat helpful idea to always surround database, table, and column names (plus anything else like a view, procedure, etc) in backticks for this very reason.

By the way, https://github.com/phpmyadmin/phpmyadmin/wiki/Garbled_data has several hints for other solutions with decoding garbled data, but I believe your problem is limited to the backticks and when you resolve that, your query should work correctly.

Upvotes: 1

Related Questions