Problems and solutions in the course of MYSQL/MARIADB learning

mysql> INSERT into student values (162111,'Zhou Yi','male', +,'CS'); ERROR1062(23000): Duplicate Entry'16211160127'  forKey'PRIMARY'(duplecate for repetition) primary key primary key cannot be inserted with the same field as Uniqueerror1025(HY000): Error on Rename of'./test/#sql -27c_2308'To'./test/student'(errno: MaxThis error occurs when you change the type encoding type generally for a foreign key constraint workaround to temporarily stop the foreign key checkSetforeign_key_checks=0the default encoding of the database may not be in the UTF8 format when the database is just installed, so the following are the workarounds for storing Chinese data:1First, the MARIADB client and the default encoding of the database are changed to UTF8 (based on Cento7.2MARIADB5.7): View mariadb/MySQL Database related code: MySQL> Show variables like'character%';+--------------------------+----------------------------+| variable_name | Value |+--------------------------+----------------------------+| character_set_client | UTF8 | | character_set_connection | UTF8 | | Character_set_database | Latin1 | | Character_set_filesystem | binary | | Character_set_results | UTF8 | | Character_set_server | Latin1 | | Character_set_system | UTF8 | | Character_sets_dir | /usr/share/mysql/charsets/|+--------------------------+----------------------------+8Rowsinch Set(0.38sec) where database and server are not in UTF8 format, enter/etc/change the configuration file in the MY.CNF.D directory: add under [Client] in CLIENT.CNF: [Client]default-character-Set=UTF8 added under [mysqld] in server.cnf: [Mysqld]character-Set-server=UTF8 Restart the database to view mariadb/MySQL Database related code: MySQL> 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 | /usr/share/mysql/charsets/|+--------------------------+----------------------------+8Rowsinch Set(0.38sec)2because the default encoding format for an established database is not UTF8, the established database does not change after the default encoding format is changed, and the original encoding format of the database is inherited by default in the newly created table, so here you can change the default encoding for a single database: First select the database you want to change MySQL>Use Testdatabase changed to view the encoding format of the test database: MySQL>Show create database test;+----------+-----------------------------------------------------------------+| Database | Create Database |+----------+--------------------------------------------- --------------------+| Test | CREATE DATABASE ' Test '/*!40100 DEFAULT CHARACTER SET latin1*/|+----------+-----------------------------------------------------------------+1Rowinch Set(0.38sec) Change the encoding format of the database: MySQL> ALTER DATABASE TestdefaultCharacterSetUTF8; Query OK,1Row affected (0.38sec) again to see if the change was successful: MySQL>Show create database test;+----------+---------------------------------------------------------------+| Database | Create Database |+----------+----------------------------------------------- ----------------+| Test | CREATE DATABASE ' Test '/*!40100 DEFAULT CHARACTER SET UTF8*/|+----------+---------------------------------------------------------------+1Rowinch Set(0.38sec) Create a new form to confirm: MySQL> CREATE TABLE EMP (empno numeric (4) Primary Key,ename varchar (Ten) notNULL, Job varchar (9), Mgr Numeric (4), HireDate date, Sal numeric (7,2), Comm Numeric (7,2), Deptno Numeric (2)); Query OK,0Rows Affected (0.41sec) To view information about the EMP table: MySQL> Show full columns fromEMP;

View the encoding in the Student table:mysql> show full columns from student;

Modify the encoding in the field:mysql> ALTER TABLE student convert to character set UTF8 collate utf8_general_ci; Error 1025 (HY000): Error on Rename of './test/#sql -27c_2308 ' to './test/student ' (errno:150) This error occurs when you change the type encoding type to have a foreign key constraint generally Workaround temporarily stop the foreign key check set foreign_key_checks=0;mysql> show full columns from student;

To check again, it can be seen that all the coding has been changed to UTF8 format;
2018-04-29

Problems and solutions in the course of MYSQL/MARIADB learning