mysql/mariadb學習過程中出現的問題與解決

mysql> insert into student values(162111,'周帥','男',21,'CS');
ERROR 1062 (23000): Duplicate entry '16211160127' for key 'PRIMARY'(duplecate為重覆的意思)
primary key 主鍵不能插入與其相同的欄位 同 unique

ERROR 1025 (HY000): Error on rename of './test/#sql-27c_2308' to './test/student' (errno: 150)
更改類型編碼類型時 出現此錯誤一般為有外鍵約束 解決方法 暫時停止外鍵檢查 set foreign_key_checks=0;

資料庫剛安裝時資料庫的預設編碼可能並不一定為utf8的格式,那麼存入中文數據時可能會出錯以下為解決方法:
1.首先將mariadb的客戶端以及資料庫的預設編碼改成utf8(基於cento7.2 mariadb5.7):
查看mariadb/mysql資料庫的相關編碼:
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/ |
+--------------------------+----------------------------+
8 rows in set (0.38 sec)
這其中有database和server不是utf8的格式,則進入/etc/my.cnf.d目錄中更改配置文件:
在client.cnf中的[client]下添加:
[client]
default-character-set=utf8
在server.cnf中的[mysqld]下添加:
[mysqld]
character-set-server=utf8
重啟資料庫,查看mariadb/mysql資料庫的相關編碼:
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/ |
+--------------------------+----------------------------+
8 rows in set (0.38 sec)
2.因為已建立的資料庫預設的編碼格式不為utf8則雖然更改預設的編碼格式後,已建立的資料庫並不會更改,在之後新創建的表格中也會預設繼承資料庫原來的編碼格式,因此這裡來更改單個資料庫的預設編碼:
首先選擇要更改的資料庫
mysql> use test
Database changed
查看test資料庫的編碼格式:
mysql> show create database test;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.38 sec)
更改資料庫的編碼格式:
mysql> alter database test default character set utf8;
Query OK, 1 row affected (0.38 sec)
再次查看是否更改成功:
mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.38 sec)
新建一個表格來確認:
mysql> create table emp(empno numeric(4) primary key,ename varchar(10) not null,job varchar(9),mgr numeric(4),hiredate date, sal numeric(7,2),comm numeric(7,2),deptno numeric(2));
Query OK, 0 rows affected (0.41 sec)
查看emp表格中的相關信息:
mysql> show full columns from emp;

查看student表中的編碼:
mysql> show full columns from student;

修改欄位中的編碼:
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)
更改類型編碼類型時 出現此錯誤一般為有外鍵約束 解決方法 暫時停止外鍵檢查 set foreign_key_checks=0;
mysql> show full columns from student;

再來查看一下,可見所有的編碼都已經改成utf8的格式;
2018-04-29