Environment: Windows 8.1+mysql 5.6.19
The first thing to say is that under Windows MySQL My.ini location is: drive letter \programdata\mysql\mysql Server 5.6, that is, under the data path, if the system disk, ProgramData folder is hidden Non-system disk This file is not hidden. Not the program files.
2. To view the My.ini file, the encoding for the default installation is UTF8.
Default-character-set=utf8
Character-set-server=utf8
Can be through show variables like '%character_set_% '
The tool I use is MySQL Workbench
3. Modifying character encoding in Workbench is only valid for this connection (and then creating a new SQL tab in this workbench is also valid, I call this new SQL tab a session, I define it myself, and the official connection and the session are not necessarily the same)
If you use the following two commands, then you create a new SQL tab to view Character_set_server and Character_set_database also become ' lantin1 '
SET @ @character_set_server = ' latin1 '
SET @ @character_set_database = ' latin1 '
You pull this workbench out, or disconnect, and then execute show variables like '%character_set_% ', and you'll find just character_set_server and character_set_ The database will become UTF8.
4. After My.ini changes the character encoding, restart the service to take effect
Change Character-set-server=utf8 to Latin1 in My.ini, restart service, use show variables like '%character_set_%,character_set_ Both server and Character_set_database =latin1
5. Next 4th step, after modifying Latin1, create a table and insert the data
create table dept_test (dept_no int primary key , Dept_name varchar not null ); #插入英文是OK的insert into Dept_test values (10, ' test ' ); #插入中文就报错insert into dept_test values (20, Test 2 ' )
Modify the My.ini again, set the Character-set-server to UTF8, and restart the MySQL service
Insert (20, ' Test 2 ') again, or report the same error
I can't figure out why MySQL on the server, I use Workbench to connect to MySQL on the server, using show varables like '%character_set_% ' found character_set_ Server and Character_set_database are latin1, query part of the table is garbled, garbled 999/1000 is Chinese, I use SET @ @character_set_server =utf8 and set @@ After Character_set_database=utf8, select query or garbled. If the code is latin1 when installing MySQL, how is Chinese inserted into the table? should also not be encrypted, because the values of the other fields in the same table are visible.
Why don't we find out tomorrow?