[TYPO3-english] How can I convert my database to utf8?
Jigal van Hemert
jigal at xs4all.nl
Wed Jun 2 21:33:01 CEST 2010
Jörg Klein wrote:
> Hi guys,
>
> I want to use utf8 in my database.
>
> What I have:
> I use a MySQL Server. character_set_xxx values are all set to utf8.
> But the database with all tables and columns uses latin1 as charset and
> latin1_german1_ci as collation.
> I have german special characters in this database.
> Umlauts are displayed correctly in TYPO3 and in PHPMyAdmin.
>
> What I want:
> I want to set $TYPO3_CONF_VARS['BE']['forceCharset'] = utf-8;
> According to the description in the Install Tool the database will then
> always use utf-8 as charset.
> So I must(?) convert the DB to utf-8.
Just a few days ago I posted this:
Back in 2008 I wrote a standalone script to convert the tables and
columns in a MySQL database to utf-8. [1]
Read the following very carefully, because you have to make a few
adjustments depending on the situation!
- the script was meant for the situation where utf-8 encoded data was
stored in latin-1 (or other charsets) tables. This was very common then.
You can recognize this by looking in PHPmyAdmin and you find that
characters with accents (diacriticals) are shown as weird
double-character combinations:
instead of "Ali Gökgöz and Gültekin Tarcan" it shows as "Ali Gökgöz
and Gültekin Tarcan".
If this is *not* the case in your situation, turn lines 97 - 107 into
comments by putting // in front of them.
- in line 19 of the script it says define("SIMULATE", TRUE); This makes
sure that the tables are not really converted, it's just a practice run;
a simulation. After you executed the script at least once and there are
no errors you can turn this into define("SIMULATE", FALSE); to do the
conversion for real.
- put the script in a subdirectory of the TYPO3 installation, for
example inside 'fileadmin'. It is designed to run from a subdirectory so
it can pick up the database connection data from localconf.php
- run the script from your browser:
http://domain.ext/fileadmin/db_utf8_fix.php It shows each table it found
and after the name of a table a dot for each column it has converted.
- it really doesn't matter if there are columns/tables already in utf-8
format
- make a backup of your database before you do such a conversion
[1] http://www.xs4all.nl/~dcbjht/typo3/db_utf8_fix.zip
Furthermore, if you use the following settings in the Install Tool you
should have a UTF-8 installation:
$TYPO3_CONF_VARS['SYS']['setDBinit'] = 'SET NAMES utf8;';
$TYPO3_CONF_VARS['BE']['forceCharset'] = 'utf-8';
Have a good one!
--
Jigal van Hemert
skype:jigal.van.hemert
msn: jigal at xs4all.nl
http://twitter.com/jigalvh
More information about the TYPO3-english
mailing list