How to correctly set the encoding in the console for the database?

Question

PHP

People. Please tell me - what is the correct sequence of actions to correctly set the encoding in the DBMS, console and output in PHP? Nothing is really explained anywhere, everyone seems to use PHPMyAdmin (everything works there). And I'm trying through the console.
1. First, I resaved all the pages of the site to utf8, then additionally specified the headers and htaccess to utf8.
2. Now the DBMS. Created a database specifying Engine = InnoDB, Charset = utf8, Collation = utf8_bin.
3. And at the 3rd moment I got stuck, for a long time - when entering through the console, everything was entered wonderfully and it was displayed well on it, but when requested already on the site (local server) through PHP, instead of Russian letters, hieroglyphs were displayed. However, if you add Russian letters through PHPMyAdmin, then everything is displayed well on the PHP side. And one more thing - what I entered in the console is also displayed in hieroglyphs in PHPMyAdmin. In the console itself, it is displayed normally.
As a result, I almost got to the solution, but how to use it correctly is not written. So:
1) The font in the console settings should be Lucida
2) Open the console -> change the encoding of the console itself (enter a choice of three: chcp 866 | chcp 1251 | chcp 65001 - respectively, this is cp866 | windows 1251 | utf8 ) - here and the first question comes up:And what encoding should I switch the console to if the database is in utf8? It would be logical to assume that the console should be switched to chcp 65001, but that was not the case - the console throws me out of mysql as soon as I try to enter Russian characters into the table. Where is the logic?
3) You can change the encoding in mysql in your database and specify in it - Set names utf8 or - Charset utf8. Open console -> chcp 65001 -> enter mysql -> charset utf8; -> create a table -> enter Russian characters into it -> throws us out again - And then another question arises: What encoding should I switch the database to in order to enter Russian characters so that they are displayed correctly both in the console, and in PHPMyAdmin, and on site on the PHP side?
It's strange, but by setting chcp 866 in the console and charset cp866 in the DBMS, everything suddenly worked for me. But this didn’t suit me, why doesn’t a completely logical and universal option with reliable utf8 work? And how to arrange the encodings correctly so that there are no unnecessary "adventures" in the future?
I supplement:
I found a hypothetical solution here click. It says 'SET NAMES cp866; - for cmd windows'. Therefore, the 'Set names' command does not set the encoding for the database by converting it, but sets the encoding for the console (for cmd Windows). This solves my problem, because now it is clear that the DB remains in utf8, it's just that the console needs cp866 for some reason to correctly interpret Russian characters. In PHPMyAdmin, it is indicated everywhere that the database and tables remained in utf8 and utf8_bin. Although these commands in the console write a little different:

mysql> SHOW VARIABLES LIKE 'character_set%'; 
+--------------------------+----------------------------------------------------------+ 
| Variable_name            | Value                                                    | 
+--------------------------+----------------------------------------------------------+ 
| character_set_client     | latin1                                                   | 
| character_set_connection | latin1                                                   | 
| character_set_database   | utf8                                                     | 
| character_set_filesystem | binary                                                   | 
| character_set_results    | latin1                                                   | 
| character_set_server     | utf8                                                     | 
| character_set_system     | utf8                                                     | 
| character_sets_dir       | c:\openserver\modules\database\MySQL-5.1\share\charsets\ | 
+--------------------------+----------------------------------------------------------+ 
8 rows in set (0.00 sec)                                                                
                                                                                        
mysql> SHOW VARIABLES LIKE 'collation%';                                                
+----------------------+-------------------+                                            
| Variable_name        | Value             |                                            
+----------------------+-------------------+                                            
| collation_connection | latin1_swedish_ci |                                            
| collation_database   | utf8_bin          |                                            
| collation_server     | utf8_bin          |                                            
+----------------------+-------------------+                                            
3 rows in set (0.00 sec)

Nowhere is it clearly written in which encoding each of these parameters should be set for trouble-free use on Russian-language sites. But since it works, I think the problem is solved.

Answer 1

This is because you are using CMD, and there, for historical reasons, the encoding is CP866. Use Mysql Workbench , or the Database Tools plugin in PhpStorm (it's subjectively better and more convenient) - everything is in order there with encodings, with auto-completion, a static analyzer and other goodies that are convenient for work.