mysql外键的使用

mysql外键的使用


像MySQL这样的关系型数据库管理系统，它们的基础是在数据库的表之间创建关系的能力。通过方便地在不同表中建 立记录到记录的联系，RDBMS可以利用不同的方法分析数据，同时保持数据库以系统的方式、最小的冗余进行组织。
像MySQL这样的关系型数据库管理系统，它们的基础是在数据库的表之间创建关系的能力。通过方便地在不同表中 建立记录到记录的联系，RDBMS可以利用不同的方法分析数据，同时保持数据库以系统的方式、最小的冗余进行组织。
简单描述：
这些关系基本上依靠外键进行管理，在关系中所有表中具有相同含义的字段作为公共部分来连接不同表中的记录。外键可以是一对一的，一个表的记录只能与另一个 表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。
MySQL中“键”和“索引”的定义相同， 所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。这和一些封建思想比较沉重 的家庭是一样的，外来的孩子(儿媳妇，倒插门女婿)一般都是不受重视的。
低俗示例：
表间一对一关系示例：
有两张表，第一张表是记录公司有多少人，都有谁，也就是员工编号及员工姓名这些基本表。另一张表记录每个月发给用户多少工资，所谓工资表是也。
但是工资表里面不能以员工姓名为主键，同样要通过员工id，因为员工的姓名是可能重复的啊。部门经理叫张三，小弟也叫张三，那这俩张三的工资能一样 吗？并且员工表里面的每个人都有工资，否则谁也不给你干活，且一个人只能有一份工资，否则老板也不同意了。所以员工表和工资表是通过员工id进行关联的一 对一关系。
不过我们要有一个好的价值观，我们上班不能为了钱，我们是为了学知识，学文化，为早日实现四个现代化(别问我是啥，也别问我到底实现没有)而努力奋 斗。所以在工资表里如果没有你也不要乱喊。嗯。
/*
建立员工表
*/
create table employees (
id int(5) not null auto_increment ,
name varchar(8) not null,
primary key (id)
)
type＝innodb;
/*
建立工资表
*/
create table payroll(
id int(5) not null,
emp_id int(5) not null,
name varchar(8) not null,
payroll float(4,2) not null,
primary key(id),
index emp_id (emp_id),
foreign key (emp_id) references employees (id)
)
type = innodb;
表间一对多关系示例：
有两个表，一个是贪官表，有贪官的id和名字。另有一张贪官情妇表，注意一个贪官不一定只有一个情妇，其有个二三四五奶是很正常的，所以在贪官表里 面的一条数据，对应情妇表里可能就有多条记录，这是通过贪官id进行关联的一对多关系。
参照完整性：
当外键与另一个表的字段有关系，而且这种关系是惟一时，这个系统就称为处于参照完整性的状态。也就是说，如果一个字段在所有的表中只出现一次，而且 每个表的这个字段的变化都会影响其他表，这就是存在参照完整性。
术语理解上可能不太方便，其实就是说要在有外键的表中保持所有数据的一致性。比如说“张三”离职了，在员工表里面肯定没有这个人了，可是如果在工资 表里面还存在这个孩子，那么老大就会很生气的。
另外，比如说一个县官，因为一些小政绩，由县官变成了知府，那么他的那些情妇的地位也要调整一下，最起码得从县官二奶改为知府二奶，否则这位二奶也 是不会同意的。
MySQL的外键只能在InnoDB表中使用：
当今主流数据库都会自动考虑参照完整性的问题。当你更新或删除数据时，其会把相关联的表中数据也都给你变过来。比如县官张三改名为王二麻子，其情妇 的称号就会自动改为王二麻子的情妇。嗯。
MySQL对此一直持观望态度，它允许使用外键，但是为了完整性检验的目的，在除了InnoDB表类型之外的所有表类型中都忽略了这个功能。这可能 有些怪异，实际上却非常正常：对于数据库的所有外键的每次插入、更新和删除后，进行完整性检查是一个耗费时间和资源的过程，它可能影响性能，特别是当处理 复杂的或者是缠绕的连接树时。因而，用户可以在表的基础上，选择适合于特定需求的最好结合。。
所以，如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基 础上保持良好的性能，最好选择表结构为innoDB类型。
MySQL创建外键语法：
创建外键的语法是这样的：FOREIGN KEY (当前表的字段名)… REFERENCES 参照表 (参照表的字段名)
foreign key (emp_id) references employees (id); 的意思就是说当前表的emp_id字段是以employees的id字段为外键的。
注意事项：
一旦建立外键，MySQL只允许向当前表中加入外键表中已有的数据列。比如说贪官表里有“王二麻子”，那么在情妇表只才能有“王二麻子的情妇”。也 就是说只有确认一个人是贪官了，才能把其情妇信息列入此表中，否则是不行滴。
关系中的所有表必须是innoDB表，在非InnoDB表中，MySQL将会忽略FOREIGN KEY…REFERENCES修饰符。
用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。
在外键关系中，字段的数据类型必须相似，这对于大小和符号都必须匹配的整数类型尤其重要。
即使表存在外键约束，MySQL还允许我们删除表，并且不会产生错误(即使这样做可能会破坏更早创建的外键)
删除外键方法：
long long ago，人们只能通过删除表来删除外键。不过现在MySQL(在4.0.13及更高版本中)提供了一种从表中删除外键比较缓和的方法，缓和与否不太清楚， 但是至少不再那么无耻。
ALTER TABLE table-name DROP FOREIGN KEY key-id;
这里有一个概念，这个外键的id是啥玩意？我们可以通过SHOW　CREATE TABLＥ 命令来获得key-id的值。日后我们详细讨论这些内容，大家可以自行演示。
/*
显示建表结构语句，key-id为payroll_ibfk_1
*/
show create table payroll \G
/*
*************************** 1. row ***************************
       Table: payroll
Create Table: CREATE TABLE `payroll` (
  `id` int(5) NOT NULL,
  `emp_id` int(5) NOT NULL,
  `name` varchar(8) NOT NULL,
  `payroll` float(4,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_id` (`emp_id`),
  CONSTRAINT `payroll_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
*/
自动键更新和删除：
外键可以保证新插入的记录的完整性。但是，如果在REFERENCES从句中从已命名的表删除记录会怎样？在使用同样的值作为外键的辅助表中会发生 什么？
很明显，那些记录也应该被删除，否则在数据库中就会有很多无意义的孤立记录。MySQL可能通过向FOREIGN KEY…REFERENCES 修饰符添加一个ON DELETE或ON UPDATE子句简化任务，它告诉了数据库在这种情况如何处理孤立任务。

请注意，通过 ON UPDATE　和ON DELETE规则，设置ＭｙＳＱＬ能够实现自动操作时，如果键的关系没有设置好，可能会导致严重的数据破坏。例如，如果一系列的表通过外键关系和ON DELETE CASCADE 规则连接时，任意一个主表的变化都会导致甚至只和原始删除有一些将要联系的记录在没有警告的情况下被删除。所以，我们在操作之前还是要检查这些规则的，操 作之后还要再次检查。
MySQL开发中的外键与参照完整性
参照完整性(Referential integrity)是数据库设计中一个重要的概念。在系统不同的列表中， 当数据库所有参照合法或非合法关联时都会涉及到参照完整性。当参照完整性存在时，任何与不存在记录的关联将变得无效化，由此可防止
参照完整性(Referential integrity)是数据库设计中一个重要的概念。在系统不同的列表中，当数据库所有参照合法或非合法关联时都会涉及到参照完整性。当参照完整性存在 时，任何与不存在记录的关联将变得无效化，由此可防止用户出现各种错误，从而提供更为准确和实用的数据库。
参照完整性通常通过外键(foreign key)的使用而被广泛应用。长久以来，流行工具开源RDBMS MySQL并没有支持外键，原因是这种支持将会降低RDBMS的速度和性能。然而，由于很多用户对参照完整性的优点倍感兴趣，最近MySQL的不同版本都通过新InnoDB列表引擎支持外键。由此，在数据库组成的列表中保持参照完整性将变得非常简单。
为了建立两个MySQL表之间的一个外键关系，必须满足以下三种情况：
两个表必须是InnoDB表类型。
使用在外键关系的域必须为索引型(Index)。
使用在外键关系的域必须与数据类型相似。
例 子是理解以上要点的最好方法。如表A所示，建立两个表，其中一个列出动物种类及相应的代码(表名为：species)，另一表列出动物园中的动物(表名 为：zoo)。现在，我们想通过species关联这两个表，所以我们只需要接受和保存zoo表中包含species表中的合法动物的入口到数据库中。
表A
mysql> CREATE TABLE species (id TINYINT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, PRIMARY KEY(id)) ENGINE=INNODB;
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO species VALUES (1, 'orangutan'), (2, 'elephant'), (3, 'hippopotamus'), (4, 'yak');
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE zoo (id INT(4) NOT NULL, name VARCHAR(50) NOT NULL, FK_species TINYINT(4) NOT NULL, INDEX (FK_species), FOREIGN KEY (FK_species) REFERENCES species (id), PRIMARY KEY(id)) ENGINE=INNODB;
注意:对于非InnoDB表， FOREIGN KEY 语句将被忽略。
现在，fieldszoo.species与species.id 之间存在一个外键关系。只有相应的zoo.specie与species.idfield的一个值相匹配，动物表中的入口才可被访问。以下的输出即演示了 当你想输入一个Harry Hippopotamus记录，而使用到不合法的species代码：
mysql> INSERT INTO zoo VALUES (1, 'Harry', 5);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
这里，MySQL核查species表以查看species代码是否存在，如果发现不存在，就拒绝该记录。当你输入正确代码的，可以与以上做比较。
mysql> INSERT INTO zoo VALUES (1, 'Harry', 3);
Query OK, 1 row affected (0.06 sec)
这里，MySQL核查species表以查看species代码是否存在，当发现存在，允许记录保存在zoo表中。
为了删除一个外键关系，首先使用SHOW CREATE TABLE找出InnoDB的内部标签，如表B所示：
表 B
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| zoo | CREATE TABLE `zoo` (
`id` int(4) NOT NULL default '0',
`name` varchar(50) NOT NULL default '',
`FK_species` tinyint(4) NOT NULL default '0',
KEY `FK_species` (`FK_species`),
CONSTRAINT `zoo_ibfk_1` FOREIGN KEY (`FK_species`)
REFERENCES `species` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------+
然后使用带有DROP FOREIGN KEY 语句的ALTER TABLE命令，如以下：
mysql> ALTER TABLE zoo DROP FOREIGN KEY zoo_ibfk_1;
Query OK, 1 row affected (0.11 sec)
Records: 1 Duplicates: 0 Warnings: 0
为了将一个外键添加到一个现成的表中，使用ADD FOREIGN KEY的 ALTER TABLE语句指定合适的域作为一个外键：
mysql> ALTER TABLE zoo ADD FOREIGN KEY (FK_species) REFERENCES species (id);
Query OK, 1 rows affected (0.11 sec)
Records: 1 Duplicates: 0 Warnings: 0
如以上例子解释的，外键在捉摸数据入口错误上起着重要的作用，由此可建立更为强健更加集成的数据库。另一方面值得提到的是，执行外键核实是内部资料 处理的过程，且不同表之间指定复杂的内部关系可以导致数据库的性能下降。所以，在参照完整性与性能考虑之间找到平衡点相当重要，而使用外键就是能够确保性 能与稳健之间的最优结合．
MySQL 5存储过程
MySQL 5.0 新特性教程是为需要了解5.0版本新特性的MySQL老用户而写的。简单的来说是介绍了“存 储过程、触发器、视图、信息架构视图”,在此感谢译者陈朋奕的努力.
MySQL 5.0 新特性教程是为需要了解5.0版本新特性的MySQL老用户而写的。简单的来说是介绍了“存储过程、触发器、视图、信息架构视图”,在此感谢译者陈朋奕的 努力.
希望这本书能像内行专家那样与您进行对话，用简单的问题、例子让你学到需要的知识。为了达到这样的目的，我会从每一个细节开始慢慢的为大家建立概 念，最后会给大家展示较大的实用例，在学习之前也许大家会认为这个用例很难，但是只要跟着课程去学，相信很快就能掌握。
Conventions and Styles 约定和编程风格
每次我想要演示实际代码时，我会对mysql客户端的屏幕就出现的代码进行调整，将字体改成Courier，使他们看起来与普通文本不一样。
在这里举个例子：mysql> DROP FUNCTION f;Query OK, 0 rows affected (0.00 sec)
如果实例比较大，则需要在某些行和段落间加注释，同时我会用将“<--”符号放在页面的右边以表示强调。
例如：
mysql> CREATE PROCEDURE p ()
-> BEGIN
-> /* This procedure does nothing */ <--
-> END;//Query OK, 0 rows affected (0.00 sec)
有时候我会将例子中的"mysql>"和"->"这些系统显示去掉，你可以直接将代码复制到mysql客户端程序中（如果你现在所读的 不是电子版的，可以在mysql.com网站下载相关脚本）所以的例子都已经在Suse 9.2 Linux、Mysql 5.0.3公共版上测试通过。
在您阅读本书的时候，Mysql已经有更高的版本，同时能支持更多OS了，包括Windows，Sparc，HP-UX。因此这里的例子将能正常的 运行在您的电脑上。但如果运行仍然出现故障，可以咨询你认识的资深Mysql用户，以得到长久的支持和帮助。
A Definition and an Example 定义及实例
定义及实例存储过程是一种存储在书库中的程序（就像正规语言里的子程序一样），准确的来说，MySQL支持的“routines（例程）”有两种： 一是我们说的存储过程，二是在其他SQL语句中可以返回值的函数（使用起来和Mysql预装载的函数一样，如pi()）。我在本书里面会更经常使用存储过 程，因为这是我们过去的习惯，相信大家也会接受。
一个存储过程包括名字，参数列表，以及可以包括很多SQL语句的SQL语句集。
在这里对局部变量，异常处理，循环控制和IF条件句有新的语法定义。
下面是一个包括存储过程的实例声明：（译注：为了方便阅读，此后的程序不添任何中文注释）
CREATE PROCEDURE procedure1 /* name存储过程名*/
(IN parameter1 INTEGER) /* parameters参数*/
BEGIN /* start of block语句块头*/
DECLARE variable1 CHAR(10); /* variables变量声明*/
IF parameter1 = 17 THEN /* start of IF IF条件开始*/
SET variable1 = 'birds'; /* assignment赋值*/
ELSE
SET variable1 = 'beasts'; /* assignment赋值*/
END IF; /* end of IF IF结束*/
INSERT INTO table1 VALUES (variable1);/* statement SQL语句*/
END /* end of block语句块结束*/
下面我将会介绍你可以利用存储过程做的工作的所有细节。同时我们将介绍新的数据库对象—触发器，因为触发器和存储过程的关联是必然的。
Why Stored Procedures 为什么要用存储过程
由于存储过程对于MySQL来说是新的功能，很自然的在使用时你需要更加注意。
毕竟，在此之前没有任何人使用过，也没有很多大量的有经验的用户来带你走他们走过的路。然而你应该开始考虑把现有程序（可能在服务器应用程序中，用 户自定义函数（UDF）中，或是脚本中）转移到存储过程中来。这样做不需要原因，你不得不去做。
因为存储过程是已经被认证的技术！虽然在Mysql中它是新的，但是相同功能的函数在其他DBMS中早已存在，而它们的语法往是相同的。因此你可以 从其他人那里获得这些概念，也有很多你可以咨询或者雇用的经验用户，还有许多第三方的文档可供你阅读。
存储过程会使系统运行更快！虽然我们暂时不能在Mysql上证明这个优势，用户得到的体验也不一样。我们可以说的就是Mysql服务器在缓存机制上 做了改进，就像Preparedstatements（预处理语句）所做的那样。由于没有编译器，因此SQL存储过程不会像外部语言（如C）编写的程序运 行起来那么快。但是提升速度的主要方法却在于能否降低网络信息流量。如果你需要处理的是需要检查、循环、多语句但没有用户交互的重复性任务，你就可以使用 保存在服务器上的存储过程来完成。这样在执行任务的每一步时服务器和客户端之间就没那么多的信息来往了。
所以存储过程是可复用的组件！想象一下如果你改变了主机的语言，这对存储过程不会产生影响，因为它是数据库逻辑而不是应用程序。存储过程是可以移植 的！当你用SQL编写存储过程时，你就知道它可以运行在Mysql支持的任何平台上，不需要你额外添加运行环境包，也不需要为程序在操作系统中执行设置许 可，或者为你的不同型号的电脑存储过程将被保存！如果你编写好了一个程序，例如显示银行事物处理中的支票撤消，那想要了解支票的人就可以找到你的程序。
它会以源代码的形式保存在数据库中。这将使数据和处理数据的进程有意义的关联这可能跟你在课上听到的规划论中说的一样。存储过程可以迁移！
Mysql完全支持SQL 2003标准。某些数据库（如DB2、Mimer）同样支持。但也有部分不支持的，如Oracle、SQL Server不支持。我们将会给予足够帮助和工具，使为其他DBMS编写的代码能更容易转移到Mysql上。
Setting up with MySQL 5.0 设置并开始MySQL 5.0服务
通过
mysql_fix_privilege_tables
或者
~/mysql-5.0/scripts/mysql_install_db
来开始MySQL服务
作为我们练习的准备工作的一部分，我假定MySQL 5.0已经安装。如果没有数据库管理员为你安装好数据库以及其他软件，你就需要自己去安装了。不过你很容易忘掉一件事，那就是你需要有一个名为 mysql.proc的表。
在安装了最新版本后，你必须运行
mysql_fix_privilege_tables
或者
mysql_install_db
（只需要运行其中一个就够了）——不然存储过程将不能工作。我同时启用在root身份后运行一个非正式的SQL脚本，如下：
mysql>source/home/pgulutzan/mysql-5.0/scripts/mysql_prepare_privilege_tables_for_5.sql
Starting the MySQL Client 启动MySQL客户端
这是我启动mysql客户端的方式。你也许会使用其他方式，如果你使用的是二进制版本或者是Windows系统的电脑，你可能会在其他子目录下运行 以下程序：
easy@phpv:~> /usr/local/mysql/bin/mysql --user=root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.3-alpha-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
在演示中，我将会展示以root身份登陆后的mysql客户端返回的结果，这样意味着我有极大的特权。
Check for the Correct Version 核对版本
为了确认使用的MySQL的版本是正确的，我们要查询版本。我有两种方法确认我使用的是5.0版本
SHOW VARIABLES LIKE 'version';
or
SELECT VERSION();
例如：
mysql> SHOW VARIABLES LIKE 'version';
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| version | 5.0.3-alpha-debug |
+---------------+-------------------+
1 row in set (0.00 sec)
mysql> SELECT VERSION();
+-------------------+
| VERSION() |
+-------------------+
| 5.0.3-alpha-debug |
+-------------------+
1 row in set (0.00 sec)
当看见数字'5.0.x' 后就可以确认存储过程能够在这个客户端上正常工作。
The Sample "Database" 示例数据库
现在要做的第一件事是创建一个新的数据库然后设定为默认数据库实现这个步骤的SQL
语句如下：
CREATE DATABASE db5;
USE db5;
例如：
mysql> CREATE DATABASE db5;
Query OK, 1 row affected (0.00 sec)
mysql> USE db5;
Database changed
在这里要避免使用有重要数据的实际的数据库然后我们创建一个简单的工作表。
实现这个步骤的SQL
语句如下：
mysql> CREATE DATABASE db5;
Query OK, 1 row affected (0.01 sec)
mysql> USE db5;
Database changed
mysql> CREATE TABLE t (s1 INT);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t VALUES (5);
Query OK, 1 row affected (0.00 sec)
你会发现我只在表中插入了一列。这样做的原因是我要保持表的简单，因为在这里并不需要展示查询数据的技巧，而是教授存储过程，不需要使用大的数据 表，因为它本身已经够复杂了。
这就是示例数据库，我们将从这个名字为t的只包含一列的表开始Pick a Delimiter 选择分隔符
现在我们需要一个分隔符，实现这个步骤的SQL语句如下：
DELIMITER //
例如：
mysql> DELIMITER //
分隔符是你通知mysql客户端你已经完成输入一个SQL语句的字符或字符串符号。一直以来我们都使用分号“；”，但在存储过程中，这会产生不少问 题，因为存储过程中有许多语句，所以每一个都需要一个分号因此你需要选择一个不太可能出现在你的语句或程序中的字符串作为分隔符。我曾用过双斜杠 “//”，也有人用竖线“|”。我曾见过在DB2程序中使用“@”符号的，但我不喜欢这样。你可以根据自己的喜好来选择，但是在这个课程中为了更容易理 解，你最好选择跟我一样。如果以后要恢复使用“;”（分号）作为分隔符，输入下面语句就可以了：
"DELIMITER ;//".
CREATE PROCEDURE Example 创建程序实例
CREATE PROCEDURE p1 () SELECT * FROM t; //
也许这是你使用Mysql创建的第一个存储过程。假如是这样的话，最好在你的日记中记下这个重要的里程碑。
CREATE PROCEDURE p1 () SELECT * FROM t; // <--
SQL语句存储过程的第一部分是“CREATE PROCEDURE”：
CREATE PROCEDURE p1 () SELECT * FROM t; // <--
第二部分是过程名，上面新存储过程的名字是p1。
Digression: Legal Identifiers 题外话：合法标识符的问题
存储过程名对大小写不敏感，因此‘P1’和‘p1’是同一个名字，在同一个数据库中你将不能给两个存储过程取相同的名字，因为这样将会导致重载。某 些DBMS允许重载（Oracle支持），但是MySQL不支持（译者话：希望以后会支持吧。）。
你可以采取“数据库名.存储过程名”这样的折中方法，如“db5.p1”。存储过程名可以分开，它可以包括空格符，其长度限制为64个字符，但注意 不要使用MySQL内建函数的名字，如果这样做了，在调用时将会出现下面的情况：
mysql> CALL pi();
Error 1064 (42000): You have a syntax error.
mysql> CALL pi ();
Error 1305 (42000): PROCEDURE does not exist.
在上面的第一个例子里，我调用的是一个名字叫pi的函数，但你必须在调用的函数名后加上空格，就像第二个例子那样。
CREATE PROCEDURE p1 () SELECT * FROM t; // <--
其中“（）”是“参数列表”。
CREATE PROCEDURE
语句的第三部分是参数列表。通常需要在括号内添加参数。例子中的存储过程没有参数，因此参数列表是空的—所以我只需要键入空括号，然而这是必须的。
CREATE PROCEDURE p1 () SELECT * FROM t; // <--
"SELECT * FROM t;"
是存储过程的主体。
然后到了语句的最后一个部分了，它是存储过程的主体，是一般的SQL语句。过程体中语句
"SELECT * FROM t;"
包含一个分号，如果后面有语句结束符号（//）时可以不写这个分号。
如果你还记得我把这部分叫做程序的主体将会是件好事，因为（body）这个词是大家使用的技术上的术语。通常我们不会将SELECT语句用在存储过 程中，这里只是为了演示。所以使用这样的语句，能在调用时更好的看出程序是否正常工作.
mysql python汉字乱码的解决方案
我们所说的编码格式一致：可以如下图所 示：character_set_client,character_set_connection,character_set_database,
我们所说的编码格式一致：可以如下图所 示：character_set_client,character_set_connection,character_set_database,
character_set_results,character_set_server编码要一致。下面说说如何将这三者调为一致。

1.首先，修改my.ini文件（该文件在mysql安装目录下）
(1)修改 my.ini(MySQL Server Instance Configuration 文件)

# CLIENT SECTION

[client]

port=3306

[mysql]

default-character-set=gbk


# SERVER SECTION

[mysqld]

default-character-set=gbk
然后修改character_set_server 这个是最麻烦的，也是最关键的，具体方法如下：
先关掉服务，
DOS-》mysql安装目录，bin

mysqladmin  -u root -p shutdown (回車)
然後系統將提示輸入密碼
因為停止mysql數據庫是需要mysql的root權限的
然后mysqld –C GBK启动服务器 –C是character_set_server的一个简写（可以也可以写成mysqld –character_set_server gbk）.
其次，创建新数据库，方法如 下:MySQLCommandLineClient下：
create database yourDB CHARACTER SET gbk;
set names 'gbk'
然后重启将mysql 服务关掉，再重启后就可以插入汉字了
完成以上操作后，在MySQLCommandLineClient下输入 use nlpprocess；(注：我新建的数据库)
show variables like 'character%'就会出现博文开头的图，也就说明字符集设置一致了。
下面我们看看修改后数据库显示情况（我的系统为 mysql5.1+administrator+querybrowser）

 
上图中的数据是C#向mysql中加入的，需要注意的是链接字符串应该如 下构造，string conmyengine="server=localhost;user=root;database=nlpprocess;port=3306;password=xxxx;charset=gbk";
下面说下python如何设置才能正确无误，没有乱码地从数据库中读取汉 字字段。
   python 读取有汉字值的表单
import MySQLdb
db=MySQLdb.connect(user='root',db='nlpprocess',passwd='xxx',host='localhost')
cursor=db.cursor()

select='select term_id,stemroot from nlpinvert where term_id between 10 and 14'
cursor.execute("set NAMES GBK")
cursor.execute(select)

for r in cursor.fetchall():
    print str(r[0])+':'+r[1]
cursor.close()
db.close()
结果如下：


14条实用的MySQL查询语句
在使用 WordPress 的过程中，某些时候需要修正一些数据库方面的问题。相比之下，直接在数据库中执行查 询语句是最便捷的。在这里，提供了 14 条非常实用的查询语句，可以帮助遇到类似问题的用户解决相应的问题。
在使用 WordPress 的过程中，某些时候需要修正一些数据库方面的问题。相比之下，直接在数据库中执行查询语句是最便捷的。在这里，提供了 14 条非常实用的查询语句，可以帮助遇到类似问题的用户解决相应的问题。
如何执行 MySQL 查询语句
通常情况下，我们可以直接进入服务器的 MySQL 命令行中执行。也可以使用非常著名的 WEB 程序 PHPMyAdmin 进行查询。如果我们想在 WordPress 后台直接进行查询，还可以使用 SQL Executionner 这个插件对数据库进行操作。
注意：虽然以下提供的查询语句都已经经过测试。但是请不要直接在生产环境下运行，或者经过一定的测试再执行，毕竟每个人的站点环境 可能略有不同。操作之前切记要对数据库进行备份操作。

1. 手动修改密码
某些情况下，我们忘记了管理员的密码。或者站点遭受攻击后，管理员密码被恶意修改了。这个时候，我们能做的就只有重新设置管理员的密码了。
以下查询语句，使用 MySQL 的 MD5() 函数将我们的密码进行 MD5 加密。
UPDATE 'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'user_login' ='admin' LIMIT 1;
来源：http://www.wprecipes.com/how-to-manually-reset-your-wordpress- password
2. 批量转换日式的作者
大多数刚使用 WordPress 的人可能都是直接使用 “admin”这个用户来直接发布日志。等您新建一个用户后，如果想将原来日志的作者批量修改为新用户的时候，可以使用以下查询语句。
在执行之前，您需要知道老用户和新用户的用户 ID。
UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;
来源：http://www.wprecipes.com/how-to-change-author-attribution-on-all- posts-at-once
3. 删除日志修订版本和其相关的信息
日志的修订版本功能在多用户共同维护一个博客的时候非常有用。不过该功能在数据库中添加过多的记录数也是很让人恼火的。
执行以下语句，可以将数据库中的日志修订版本记录以及其中的其他信息（包括自定义区域等等）删除掉。执行后，会节省很多数据库空间。
DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);
来源：http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql- queries-you-wish-you-knew-earlier/
4. 批量删除垃圾评论
在您的博客没有安装 Akismet 或其他替代品的前提下，一段时间没有进入后台进行评论审核。可能会出现几百甚至上千条未审核的评论。而且大部分可能是垃圾评论。这个时候，如果您在后台进 行删除，可能会浪费掉您很多时间。以下语句可以在数据库中批量删除掉所有未审核的评论。
奉劝一句，还是安装 Akismet 或类似的插件吧，可以省却您很多烦恼。
DELETE from wp_comments WHERE comment_approved = '0';
来源：http://www.wprecipes.com/mark-asked-how-to-batch-deleting-spam- comments-on-a-wordpress-blog
5. 查找未使用的标签
标签的记录是存放在 wp_terms 这个数据表中的。有时，可能部分标签创建了但是没有被使用，他们仍然会在数据表中。以下语句可以对这些未使用的标签进行查询，您可以安全的将其删除。
SELECT * From wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;
来源：http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql- queries-you-wish-you-knew-earlier/
6. 查找并替换数据
该语句不光是在 WordPress 的使用过程中能用到。您可以在很多地方用到该 MySQL 查询语句。其使用 MySQL 的 replace() 函数将一段字符串替换成您指定的另一段字符串。
UPDATE table_name SET field_name = replace( field_name, 'string_to_find', 'string_to_replace' ) ;
来源：http://perishablepress.com/press/2007/07/25/mysql-magic-find-and- replace-data/
7. 获得发表评论访问者的电子邮箱地址列表
该语句可以返回一个在您博客上进行评论的用户的电子邮箱地址列表。语句中的 DISTINCT 参数用来保证每个电子邮箱地址只显示一次。
SELECT DISTINCT comment_author_email FROM wp_comments;
来源：http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql- queries-you-wish-you-knew-earlier/
8. 一次性禁用掉所有插件
虽然新版的 WordPress 已经可以直接在后台对插件进行批量的启用和禁用操作。但是当某些插件引起整个站点不能正常访问的时候，我们就可以使用以下语句禁用掉站点内所有的插件，然 后再进入到 WordPress 后台进行调试操作。
UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';
来源：http://www.wprecipes.com/how-to-disable-all-your-plugins-in-a- second
9. 删除所有标签
标签的信息是存放在 wp_terms 数据表中的。而且，是和分类以及其他一些信息存放在相同一个表中。如果您想删除所有的标签记录，是无法简单的清空 wp_terms 这个数据表的。这会导致分类信息丢失。
执行以下语句，可以仅仅删除掉标签记录以及与日志的关联信息，而其他的分类等信息不会发生改变。
DELETE a,b,c
FROM
database.prefix_terms AS a
LEFT JOIN database.prefix_term_taxonomy AS c ON a.term_id = c.term_id
LEFT JOIN database.prefix_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id
WHERE (
c.taxonomy = 'post_tag' AND
c.count = 0
);
来源：http://wordpress.org/support/topic/311665
10. 列举无用的日志信息
日志的 Meta 信息通常是由插件或自定义区域建立的。它们非常实用，但也会让您的数据库尺寸快速的变得臃肿。以下语句会显示 postmeta 表中那些没有在 post 表有对应关系的记录，便于您对数据库进行清理。
SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
来源：http://wordpress.org/support/topic/337412
11. 禁用老日志的评论功能
某些时候，您希望把以前的日志评论功能关闭掉。可能导致您这么作出决定的原因有很多，您可以使用以下语句将指定日期之前发布的日志评论功能关闭掉。
UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2009-01-01' AND post_status = 'publish';
来源：http://perishablepress.com/press/2008/02/20/wordpress-discussion- management-enable-or-disable-comments-and-pingbacks-via-sql/
12. 替换评论者的链接
上文提到了 MySQL 的 replace() 函数。该条语句就是一个很好的例子。如果您的站点域名发生了改变，您可以使用该语句将您在自己站上发表的评论中的链接批量替换为新的域名。
UPDATE wp_comments SET comment_author_url = REPLACE( comment_author_url, 'http://oldurl.com', 'http://newurl.com' );
来源：http://perishablepress.com/press/2008/07/14/wordpress-link-author- comments-home-page/
13. 替换评论者的电子邮箱地址
和上一条差不多，批量替换掉电子邮箱地址。
UPDATE wp_comments SET comment_author_email = REPLACE( comment_author_email, '[e-mail]', '[e-mail]' );
来源：http://perishablepress.com/press/2008/05/18/wordpress-tip-update- email-address-in-the-wordpress-database
14. 删除包含指定链接的评论
某些垃圾评论发布者还是很会钻空子的。经常发布一些貌似与文章相关的评论，可评论内容中通常包含恶意网站的链接。等你发现的时候，可能这样的评论已 经很多了。使用以下代码，可以方便的将包含指定链接地址的评论一次性删除掉。
DELETE from wp_comments WHERE comment_author_url LIKE "%wpbeginner%" ;
来源：http://perishablepress.com/press/2007/07/25/mysql-magic-find-and- replace-data/
一定要修改的mysql默认预设值
关于mysql默认预设值的问题,其中提到了有4点是无论如何都要修改的.
首先是 max_connect_errors , 当某一个网站送了很多错误的请求时,mysql服务器就会当掉这个网站的权限.
第三个是 skip-name-resolve 这个设定很变态的,每一次链接都要做一次反DNS查询,mysql跑反DNS查询有什么用,浪费时间和资源.
最后我在my.cnf配置文件添加了以下语句:
max_connect_errors = 1844674407370954751
connect_timeout = 30
skip-name-resolve
slave_net_timeout = 30