MySQL学习错误总结篇：遇到并处理过的错误-持续更新

记录学习MySQL过程中遇到并处理过的一些异常——持续更新

1.MySQL安装报错(博文http://blog.csdn.net/hwhmh2010/article/details/52984890中遇到的报错)
***** Installing MySQL...******
CMake Error: The source directory "/root/soft/mysql-5.6.25-linux-glibc2.5-x86_64" does not appear to contain CMakeLists.txt.
Specify --help for usage, or press the help button on the CMake GUI.
execute command ==> make && make install
make: *** No targets specified and no makefile found.  Stop.

原因：安装包下载地址错误，重新 更新下面两个下载地址的参数变量
WEBSITEPATH=http://downloads.mysql.com/archives/get/file/ 改为 WEBSITEPATH=http://downloads.mysql.com/archives/get/file/ 
SOFTEDITION=mysql-5.6.25-linux-glibc2.5-x86_64 改为 SOFTEDITION=mysql-5.6.25

2.truncate表报错
[SQL]
truncate table s_department;
[Err] 1701 - Cannot truncate a table referenced in a foreign key constraint (`dba_test_abc`.`s_user`, CONSTRAINT `FK_jdi1gsiep4tepn7f2p95cigq2` FOREIGN KEY (`dept`) REFERENCES `dba_test_abc`.`s_department` (`code`))
原因：MySQL主外键关系的表，不允许truncate，此例中s_user.dept列对s_department.code列有外键依赖
查看表之间关系：
select distinct TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME <>'' and REFERENCED_COLUMN_NAME='code'

查看表结构(结果略)：
show create table s_department;
show create table s_user;
处理方法：
1.先SET FOREIGN_KEY_CHECKS=0 然后truncate table，再SET FOREIGN_KEY_CHECKS=1
2.先备份数据库和表之间的外键关系，然后删除外键，再truncate，最后添加外键关系
ALTER TABLE dba_test_abc.s_user DROP FOREIGN KEY FK_jdi1gsiep4tepn7f2p95cigq2;
ALTER TABLE s_user ADD CONSTRAINT FK_jdi1gsiep4tepn7f2p95cigq2 FOREIGN KEY(dept) REFERENCES s_department(code);
3.如果表中数据不多，可直接delete替代truncate（注：delete会产生远远大于truncate的事务日志，如果表巨大，此法不可取）