Php set names utf8mb4

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Update PDO example — SET NAMES utf8mb4 #734

Update PDO example — SET NAMES utf8mb4 #734

Comments

SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES ‘utf8mb4’ tells the server, “future incoming messages from this client are in character set utf8mb4. It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use a SELECT statement.)

I would suggest to update the PDO example like this:

$link = new PDO( 'mysql:host=your-hostname;dbname=your-db;charset=utf8mb4', 'your-username', 'your-password', array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_PERSISTENT => false, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci" ) );

Edit: Added COLLATE utf8mb4_unicode_ci

The text was updated successfully, but these errors were encountered:

Источник

mysqli_set_charset

Sets the character set to be used when sending data from and to the database server.

Parameters

Procedural style only: A mysqli object returned by mysqli_connect() or mysqli_init()

The desired character set.

Return Values

Returns true on success or false on failure.

Errors/Exceptions

If mysqli error reporting is enabled ( MYSQLI_REPORT_ERROR ) and the requested operation fails, a warning is generated. If, in addition, the mode is set to MYSQLI_REPORT_STRICT , a mysqli_sql_exception is thrown instead.

Examples

Example #1 mysqli::set_charset() example

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «test» );

printf ( «Initial character set: %s\n» , $mysqli -> character_set_name ());

/* change character set to utf8mb4 */
$mysqli -> set_charset ( «utf8mb4» );

printf ( «Current character set: %s\n» , $mysqli -> character_set_name ());

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$link = mysqli_connect ( ‘localhost’ , ‘my_user’ , ‘my_password’ , ‘test’ );

printf ( «Initial character set: %s\n» , mysqli_character_set_name ( $link ));

/* change character set to utf8mb4 */
mysqli_set_charset ( $link , «utf8mb4» );

printf ( «Current character set: %s\n» , mysqli_character_set_name ( $link ));

The above examples will output something similar to:

Initial character set: latin1 Current character set: utf8mb4

Notes

Note:

To use this function on a Windows platform you need MySQL client library version 4.1.11 or above (for MySQL 5.0 you need 5.0.6 or above).

Note:

This is the preferred way to change the charset. Using mysqli_query() to set it (such as SET NAMES utf8 ) is not recommended. See the MySQL character set concepts section for more information.

See Also

• mysqli_character_set_name() — Returns the current character set of the database connection
• mysqli_real_escape_string() — Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection
• MySQL character set concepts
• » List of character sets that MySQL supports

User Contributed Notes 5 notes

Setting the charset (it’s really the encoding) like this after setting up your connection:
$connection->set_charset(«utf8mb4»)

FAILS to set the proper collation for the connection:

character_set_client: utf8mb4
character_set_connection: utf8mb4
character_set_database: utf8mb4
character_set_filesystem: binary
character_set_results: utf8mb4
character_set_server: utf8mb4
character_set_system: utf8
collation_connection: utf8mb4_general_ci collation_database: utf8mb4_unicode_ci
collation_server: utf8mb4_unicode_ci

If you use SET NAMES, that works:
$connection->query(«SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci»);

character_set_client: utf8mb4
character_set_connection: utf8mb4
character_set_database: utf8mb4
character_set_filesystem: binary
character_set_results: utf8mb4
character_set_server: utf8mb4
character_set_system: utf8
collation_connection: utf8mb4_unicode_ci collation_database: utf8mb4_unicode_ci
collation_server: utf8mb4_unicode_ci

Please note, that I set the following variables on the server:

Set the following to be: utf8mb4_unicode_ci

character-set-client-handshake = FALSE or 0
skip-character-set-client-handshake = TRUE or 1

So in my case, I had tried changing the collation from utf8mb4_unicode_ci for mysql and had to change it to uft8_general_ci.

mysqli_set_charset( $con, ‘utf8’);

right before I did the SELECT command.

This is my code for reading from db :

$con = mysqli_connect($DB_SERVER, $DB_USER_READER, $DB_PASS_READER, $DB_NAME, $DB_PORT);//this is the unique connection for the selection

mysqli_set_charset( $con, ‘utf8’);

$slct_stmnt = «SELECT «.$SELECT_WHAT.» FROM «.$WHICH_TBL.» WHERE «.$ON_WHAT_CONDITION;

$slct_query = mysqli_query($con, $slct_stmnt);

if ($slct_query==true) //Do your stuff here . . .
>

And it worked like a charm. All the best. The above code can work with reading chineese, russian or arabic or any international language from the database’s table column holding such data.

Although the documentation says that using that function is preferred than using SET NAMES, it is not sufficient in case you use a collation different from the default one:

// That will reset collation_connection to latin1_swedish_ci
// (the default collation for latin1):
$mysqli -> set_charset ( ‘latin1’ );

// You have to execute the following statement *after* mysqli::set_charset()
// in order to get the desired value for collation_connection:
$mysqli -> query ( «SET NAMES latin1 COLLATE latin1_german1_ci» );

To align both the character set (e.g., utf8mb4) AND the collation sequence with the schema (database) settings:

$mysqli = new mysqli ( DB_HOST , DB_USER , DB_PASSWORD , DB_SCHEMA , DB_PORT );
if ( 0 !== $mysqli -> connect_errno )
throw new \ Exception ( $mysqli -> connect_error , $mysqli -> connect_errno );

if ( TRUE !== $mysqli -> set_charset ( ‘utf8mb4’ ) )
throw new \ Exception ( $mysql -> error , $mysqli -> errno );

if ( TRUE !== $mysqli -> query ( ‘SET collation_connection = @@collation_database;’ ) )
throw new \ Exception ( $mysql -> error , $mysqli -> errno );
?>

To confirm:

echo ‘character_set_name: ‘ , $mysqli -> character_set_name (), ‘
‘ , PHP_EOL ;
foreach( $mysqli -> query ( «SHOW VARIABLES LIKE ‘%_connection’;» )-> fetch_all () as $setting )
echo $setting [ 0 ], ‘: ‘ , $setting [ 1 ], ‘
‘ , PHP_EOL ;
?>

will output something like:
character_set_name: utf8mb4
character_set_connection: utf8mb4
collation_connection: utf8mb4_unicode_520_ci

Note that using utf8mb4 with this function may cause this function to return false, depending on the MySQL client library compiled into PHP. If the client library is older than the introduction of utf8mb4, then PHP’s call of the libraries ‘mysql_set_character_set’ will return an error because it won’t recognise that character set.

The only way you will know there’s an error is by checking the return value, because PHP warnings are not emitted by this function.
mysqli_error will return something like:
«Can’t initialize character set utf8mb4 (path: /usr/share/mysql/charsets/)»
(I don’t think the directory has anything to do with it; I think the utf8mb4 vs utf8 distinction is handled internally)

A workaround is to recall with utf8, then do a ‘SET NAMES’ query with utf8mb4.

If your MySQL server is configured to use utf8 by default, then you may not notice any of this until you get obscure bugs. It seems it will still save into the database correctly in terms of bytes. However, you may get «Data too long for column» errors if you are truncating strings to fit fields, because from MySQL’s point of view during the length check every 4-byte character will actually be multiple individual characters. This caused me hours of debugging.

Источник

Как перейти с utf8 на utf8mb4 в MySQL

Если ваша версия СУБД MySQL 5.5.3 и выше, то вам необходимо использовать кодировку utf8mb4, вместо utf8. Об этом упоминается здесь и здесь.

Следовательно, больше нет необходимости использовать ни utf8_general_ci, ни utf8_unicode_ci.

utf8mb4_general_ci или utf8mb4_unicode_ci

В настоящее время для баз данных и таблиц MySQL рекомендуется использовать кодировку utf8mb4_unicode_ci.

Настройка кодировки utf8mb4 для СУБД MySQL

Исходя из вышеизложенного нам необходимо произвести настройку основных параметров кодировки СУБД MySQL.

В конфигурационном файле MySQL ( my.ini (windows)/ my.cnf (Linux)) необходимо изменить кодировку на utf8mb4:

[client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE init_connect ='SET collation_connection = utf8mb4_unicode_ci' init_connect ='SET NAMES utf8mb4' character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci

Проверяем корректность работы применимых настроек:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

+--------------------------+--------------------+ | Variable_name | Value | +--------------------------+--------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | collation_connection | utf8mb4_general_ci | | collation_database | utf8mb4_unicode_ci | | collation_server | utf8mb4_unicode_ci | +--------------------------+--------------------+ 10 rows in set, 1 warning (0.00 sec)

Кодировка и сравнение для базы данных, таблиц и столбцов в MySQL

Запросы для измениния кодировки и сравнения для базы данных, таблиц и столбцов на utf8mb4 .

Для базы данных:

ALTER DATABASE [db_name] CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Для таблицы:

ALTER TABLE [table_name] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Для столбцов:

ALTER TABLE [table_name] CHANGE [column_name] [column_name] VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Восстановление и оптимизация всех таблиц

После обновления версии MySQL сервера и применения действий по смене кодировки и сравнений, необходимо произвести восстановление и оптимизацию всех баз данных и таблиц. Для этого вы можете выполнить следующие запросы для каждой таблицы:

REPAIR TABLE [table_name]; OPTIMIZE TABLE [table_name];

Или с использованием команды mysqlcheck :

$ mysqlcheck -u root -p --auto-repair --optimize --all-databases

Пример миграции для Yii2

В этом примере мы изменим кодировку для столбца content в таблице post :

/** * @return void * @throws \yii\db\Exception */ public function safeUp() < $sql = "ALTER TABLE `post` CHANGE `content` `content` MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"; Yii::$app->db->createCommand($sql)->execute(); > /** * @return void * @throws \yii\db\Exception */ public function safeDown() < $sql = "ALTER TABLE `post` CHANGE `content` `content` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci"; Yii::$app->db->createCommand($sql)->execute(); >

Источник