php连接mysql乱码_php连接MySQL数据库和控制台MySQL数据库中文乱码问题？

1.环境是：

是用的xampp自带的数据库配置的。

字符集信息：

MariaDB [testdb2]> show variables like 'character%';

+--------------------------+--------------------------------+

| Variable_name | Value |

+--------------------------+--------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | X:\xampp\mysql\share\charsets\ |

+--------------------------+--------------------------------+

2.问题出现在：

操作1：用php插入数据(包含中文)，并在浏览器中显示查询结果。

操作2：用控制台插入数据(包含中文)，并在控制台显示查询结果。

Case1：

因为操作台是gbk编码，所以如果set names gbk; 并且数据库、数据表都设置gbk，那么操作2正常，但用php查询并将查询结果返回在浏览器中就乱码。

控制台中代码如下：

MariaDB [testdb2]> show variables like 'character%';

+--------------------------+--------------------------------+

| Variable_name | Value |

+--------------------------+--------------------------------+

| character_set_client | gbk |

| character_set_connection | gbk |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | gbk |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | X:\xampp\mysql\share\charsets\ |

+--------------------------+--------------------------------+

8 rows in set (0.00 sec)

MariaDB [testdb2]> select * from ceshibiao

-> ;

Empty set (0.00 sec)

MariaDB [testdb2]> insert into ceshibiao(hanzi,pinyin) values("哈哈哈","hhh");

Query OK, 1 row affected (0.08 sec)

MariaDB [testdb2]> select * from ceshibiao;

+--------+--------+

| hanzi | pinyin |

+--------+--------+

| 哈哈哈 | hhh |

+--------+--------+

1 row in set (0.01 sec)

PHP代码如下：

header("Content-Type:text/html;charset=utf-8");

$dbhost = 'localhost:3306';

$dbuser = 'zzjzxh';

$dbpass = '*******';

$dbname = 'testdb2';

$mysqli = new mysqli($dbhost,$dbuser,$dbpass,$dbname);

if ($mysqli->connect_error)

{

echo "database error";

echo "
";

}

else

{

echo "php env successfully";

echo "
";

}

$sql = "SELECT hanzi,pinyin FROM ceshibiao;";

$result = $mysqli->query($sql)->fetch_array();

print($sql);

print("----");

print_r($result);

print("
");

?>

结果：

//我的想法是：在存储过程中是用的gbk编码，但在显示时使用utf-8编码，所以导致解码时错误出现乱码。

Case2：

在控制台输入chcp 65001

控制台也使用utf-8编码

控制台查看数据库字符集信息：

MariaDB [testdb2]> show variables like 'character%';

+--------------------------+--------------------------------+

| Variable_name | Value |

+--------------------------+--------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | X:\xampp\mysql\share\charsets\ |

+--------------------------+--------------------------------+

8 rows in set (0.00 sec)

MariaDB [testdb2]> select * from ceshibiao;

Empty set (0.00 sec)

PHP进行插入和查询：

header("Content-Type:text/html;charset=utf-8");

$dbhost = 'localhost:3306';

$dbuser = 'zzjzxh';

$dbpass = '*******';

$dbname = 'testdb2';

$mysqli = new mysqli($dbhost,$dbuser,$dbpass,$dbname);

if ($mysqli->connect_error)

{

echo "database error";

echo "
";

}

else

{

echo "php env successfully";

echo "
";

}

$mysqli->query("set names utf8;");

$check = $mysqli->query("insert into ceshibiao(hanzi,pinyin) values('啊啊','aa');");

if (!$check) print("插入失败！
");

$sql = "SELECT hanzi,pinyin FROM ceshibiao;";

$result = $mysqli->query($sql)->fetch_array();

print($sql);

print("----");

print_r($result);

print("
");

?>

结果:

是可以正常显示中文的。在控制台中：

MariaDB [testdb2]> select * from ceshibiao;

+--------+--------+

| hanzi | pinyin |

+--------+--------+

| 啊啊 | aa |

+--------+--------+

1 row in set (0.00 sec)

也是可以查询的。

但是在控制台中插入中文就会出错：

MariaDB [testdb2]> truncate table ceshibiao;

Query OK, 0 rows affected (0.33 sec)

MariaDB [testdb2]> insert into ceshibiao(hanzi,pinyin) values("搜得死","sds");

Query OK, 1 row affected (0.09 sec)

MariaDB [testdb2]> select * from ceshibiao;

+-------+--------+

| hanzi | pinyin |

+-------+--------+

| ??? | sds |

+-------+--------+

1 row in set (0.00 sec)

再用PHP查询结果：

同样乱码。

3.我的需求：

实现PHP对数据库插入、查询和控制台对数据库插入、查询都能够正常显示中文。