mysqli::set_charset

mysqli_set_charset

(PHP 5 >= 5.0.5, PHP 7, PHP 8)

mysqli::set_charset -- mysqli_set_charset — 设置默认字符编码

说明

面向对象风格

mysqli::set_charset ( string $charset ) : bool

过程化风格

mysqli_set_charset ( mysqli $link , string $charset ) : bool

设置在数据库间传输字符时所用的默认字符编码。

参数

link: 仅以过程化样式：由mysqli_connect() 或 mysqli_init() 返回的链接标识。
charset: 被设为默认的字符编码名。

返回值

成功时返回 true，或者在失败时返回 false。

注释

Note:
如果在Windows平台上使用该方法，需要4.1.11版或以上的MySQL客户端库，且MySQL版本为5.0.6以上。

Note:
这应该是首选的用于改变字符编码的方法，不建议使用mysqli_query()执行SQL请求的SET NAMES ...（如 SET NAMES utf8）。详见MySQL字符集的概念

范例

Example #1 mysqli::set_charset() example

面向对象风格


<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* change character set to utf8 */
if (!$mysqli->set_charset("utf8")) {
    printf("Error loading character set utf8: %s\n", $mysqli->error);
} else {
    printf("Current character set: %s\n", $mysqli->character_set_name());
}

$mysqli->close();
?>

过程化风格


<?php
$link = mysqli_connect('localhost', 'my_user', 'my_password', 'test');

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* change character set to utf8 */
if (!mysqli_set_charset($link, "utf8")) {
    printf("Error loading character set utf8: %s\n", mysqli_error($link));
} else {
    printf("Current character set: %s\n", mysqli_character_set_name($link));
}

mysqli_close($link);
?>

以上例程会输出：

Current character set: utf8

参见

mysqli_character_set_name() - 返回当前数据库连接的默认字符编码
mysqli_real_escape_string() - 根据当前连接的字符集，对于 SQL 语句中的特殊字符进行转义
» 关于MySQL支持字符集的列表

User Contributed Notes

ASchmidt at Anamera dot net 19-Jun-2018 10:54


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



<?php

$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:



<?php 

echo 'character_set_name: ', $mysqli->character_set_name(), '<br />', PHP_EOL;

foreach( $mysqli->query( "SHOW VARIABLES LIKE '%_connection';" )->fetch_all() as $setting )

    echo $setting[0], ': ', $setting[1], '<br />', PHP_EOL;

?>



will output something like:

character_set_name: utf8mb4

character_set_connection: utf8mb4

collation_connection: utf8mb4_unicode_520_ci

chris at ocproducts dot com 03-Jan-2018 07:30


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.

Konstantin Rozinov 18-Sep-2017 02:20


The comment by Claude (http://php.net/manual/en/mysqli.set-charset.php#121067) is CORRECT.



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 <---- still says general

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 <-- now says unicode

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

character_set_connection

character_set_database

character_set_results

character_set_server



collation_connection 

collation_server



Set:



character-set-client-handshake = FALSE or 0

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

claude dot pache at gmail dot com 11-May-2017 08:01


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:



<?php

// 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");

Emmanuel Appiah 20-Mar-2016 05:46


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.



Then pasted : 



mysqli_set_charset( $con, 'utf8');



right before I did the SELECT command.



This is my code for reading from db :



/*



$DB_SERVER="db_server_name";

$DB_USER_READER="root";

$DB_PASS_READER="passw*rd";

$DB_NAME="db_name";

$DB_PORT="port number";



$SELECT_WHAT="`name_of_column_as_in_your_table`";

$WHICH_TBL="`table_name`";

$ON_WHAT_CONDITION="`id`='7'";



*/



$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.