change default collation in phpmyadmin

Question

It seems to me that phpMyAdmin imports tables by default with collation latin1_swedish_ci, how i change this?

[This answer shows how to change it from phpMyAdmin](http://stackoverflow.com/a/12719854/731314). — Gary, Aug 17 '15 at 22:05
@Gary It does not, it only shows how to change collation on existing database. — ado387, Jun 02 '20 at 14:16

score 23 · Accepted Answer · answered Sep 30 '08 at 16:17

23

In your Mysql configuration change the default-character-set operative under the [mysqld] tab. For example:

[mysqld]
default-character-set=utf8

Don't forget to restart your Mysql server afterwards for the changes to take effect.

answered Sep 30 '08 at 16:17

Eran Galperin

86,251
24
115
132

2

This is not valid in debian's 5.5.28-1-log, default-character-set is invalid value. – Skynet Jan 03 '13 at 20:40
17

The above solution did not work, I did this instead: `[mysqld] character-set-server=utf8 collation-server=utf8_general_ci` – Jared Dunham Nov 19 '14 at 20:21

score 21 · Answer 2 · edited May 23 '18 at 00:48

21

For Linux:

You need to have access to the MySQL config file.
The location can vary from /etc/mysql/my.cnf to ~/my.cnf (user directory).

Add the following lines in the section [mysqld]:

collation_server = utf8_unicode_ci
character_set_server=utf8

Restart the server:
```
service mysqld restart
```

edited May 23 '18 at 00:48

Parto

168
2
13

answered Jul 07 '12 at 10:21

Yasen

3,400
1
27
22

Tomalak · Answer 3 · 2008-09-30T16:29:11.240

This is not a phpMyAdmin question.

Collations are part of recent MySQL releases, you must set the default collation of the server (or at least of the database) to change that behaviour.

To convert already imported tables to UTF-8 you can do (in PHP):

$dbname = 'my_databaseName';
mysql_connect('127.0.0.1', 'root', '');
mysql_query("ALTER DATABASE `$dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
$res = mysql_query("SHOW TABLES FROM `$dbname`");
while($row = mysql_fetch_row($res)) {
   $query = "ALTER TABLE {$dbname}.`{$row[0]}` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
   mysql_query($query);
   $query = "ALTER TABLE {$dbname}.`{$row[0]}` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
   mysql_query($query);
}
echo 'all tables converted';

Code snippet taken from here.

score 12 · Answer 4 · answered Jul 24 '18 at 16:52

12

For utf8mb4, add/change the following in the [mysqld] section:

collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

Then restart the mysql service (for Ubuntu the command is sudo service mysql restart)

answered Jul 24 '18 at 16:52

MAX POWER

5,213
15
89
141

From [their document](https://mariadb.com/kb/en/setting-character-sets-and-collations/) on **Example: Changing the Default Character Set To UTF-8**, they use `collation-server = utf8mb4_unicode_ci` and `character-set-server = utf8mb4` under `[mysqld]` group. The difference is `-` and `_` but I don't know did they all work on all OS or not but I use `-` and it work fine. Up voted. – vee Jun 04 '22 at 15:44

score 9 · Answer 5 · answered Sep 02 '13 at 08:44

9

know this is an old post. But the way i changed the default charset through phpMyAdmin was:

phpMyadmin main page > Variables tab (Server variables and settings) > searched for "character" and changed all variables from "latin1" to "utf8". (on a MAMP installation with phpMyAdmin v. 3.5.7)

And as the others said, this is the variables for MySQL and not some phpMyAdmin specific ones.

answered Sep 02 '13 at 08:44

Thor A. Pedersen

1,122
4
18
32

It lives until the next WAMP restart. How can I save these settings? – texnic Oct 11 '14 at 21:13
I was using MAMP when answering this question, not sure if MAMP and WAMP are configured the same way. But I don't use those installations anymore, I make it from scratch.. Maybe someone else can help?? – Thor A. Pedersen Oct 13 '14 at 10:59
Just got Windows again, an ran into above problem as well, where wamp reset the config on restart. I solved this by following the following post: http://stackoverflow.com/a/14194212/900774 – Thor A. Pedersen Apr 12 '16 at 11:20

score 5 · Answer 6 · answered Jan 25 '18 at 07:49

MySQL DB « change Collation Name of a Database|Table to utf8_general_ci inorder to support Unicode.

Change Configuration settings file also

XAMPP: uncomment UTF 8 Settings from the Configuration settings file « D:\xampp\mysql\bin\my.ini

## UTF 8 Settings
#init-connect=\'SET NAMES utf8\'
collation_server=utf8_unicode_ci
character_set_server=utf8
skip-character-set-client-handshake
character_sets-dir="D:/xampp/mysql/share/charsets"

For MySQL server to support UTF8 and the below line of code in file my.cnf

## UTF 8 Settings
collation_server=utf8_unicode_ci
character_set_server=utf8

@see

score 4 · Answer 7 · answered Sep 02 '19 at 17:55

Insert this strings into my.ini (or my.cnf)

[mysqld]
collation_server = utf8_unicode_ci
character_set_server=utf8

Add into config.inc.php

$cfg['DefaultConnectionCollation'] = 'utf8_general_ci';

You need to restart MySQL server and remove cookies,data for domain where is located PhpMyAdmin
Reload page with PhpMyAmdin and at look the result

score 1 · Answer 8 · answered Nov 12 '22 at 12:57

The original question was for the default setting in phpMyAdmin. I think the question relates to creating a NEW database in phpMyAdmin - when the page shows utf8mb4... This IS a phpMyAdmin setting! If you want the default for creating new databases to show utf8_general_ci. Add the following 2 line to your config.inc.php file:

$cfg['DefaultCharset'] = 'utf8';

$cfg['DefaultConnectionCollation'] = 'utf8_general_ci';

See phpMyAdmin docs https://docs.phpmyadmin.net/en/latest/config.html and https://docs.phpmyadmin.net/en/latest/config.html#cfg_DefaultConnectionCollation

score 0 · Answer 9 · answered Jan 09 '20 at 14:23

0

I wanted to use utf8mb4 instead, and the configuration had to be the following:

collation_server = utf8mb4_general_ci
character_set_server=utf8mb4

the server would not start if the character_set was set to utf8

answered Jan 09 '20 at 14:23

Nelson

2,040
17
23

change default collation in phpmyadmin

9 Answers9

Linked