2006-09-01 12:08
18.9 怎样重新设置一个忘记的口令
如果你忘记了MySQL的root
用户的口令,你可以用下列过程恢复它。
- 通过发送一个
kill
(不是kill -9
)到mysqld
服务器来关闭mysqld服务器。pid 被保存在一个.pid
文件中,通常在MySQL数据库目录中:kill `cat /mysql-data-directory/hostname.pid`
你必须是一个UNIX root
用户或运行服务器的相同用户做这个。
- 使用
--skip-grant-tables
选项重启mysqld
。
- 用
mysql -h hostname mysql
连接mysqld服务器并且用一条GRANT
命令改变口令。见7.26 GRANT
和REVOKE
句法。你也可以用mysqladmin -h hostname -u user password 'new password'
进行。
- 用
mysqladmin -h hostname flush-privileges
或用SQL命令FLUSH PRIVILEGES
来装载权限表。
18.10 文件许可权限问题
如果你有与文件许可有关的问题,例如,如果当你创建一张表时,mysql
发出下列错误消息:
ERROR: Can't find file: 'path/with/filename.frm' (Errcode: 13)
那么可能是在mysqld
启动时,环境变量UMASK
可能设置不正确。缺省的umask值是0660
。你可以如下启动safe_mysqld
改变其行为:
shell> UMASK=384 # = 600 in octal
shell> export UMASK
shell> /path/to/safe_mysqld &
18.11 文件没找到
如果你从MySQL得到ERROR '...' not found (errno: 23)
, Can't open file: ... (errno: 24)
或任何其他有errno 23
或errno 24
的错误,它意味着,你没有为MySQL分配足够的文件描述符。你能使用perror
实用程序得到错误号含义是什么的描述:
shell> perror 23
File table overflow
shell> perror 24
Too many open files
这里的问题是mysqld
正在试图同时保持打开太多的文件。你也可以告诉mysqld
一次不打开那么多的文件,或增加mysqld
可得到的文件描述符数量。
为了告诉mysqld
一次保持打开更少的文件,你可以通过使用safe_mysqld
的-O table_cache=32
选项(缺省值是64)使表缓冲更小。减小max_connections
值也将减少打开文件的数量(缺省值是90)。
要想改变mysqld
可用的文件描述符数量,修改safe_mysqld
脚本。脚本中有一条注释了的行ulimit -n 256
。你可以删除'#'
字符来去掉该行的注释,并且改变数字256改变为mysqld
可用的文件描述符的数量。
ulimit
能增加文件描述符的数量,但是只能到操作系统强加的限制。如果你需要增加每个进程可用的文件描述符数量的OS限制,参见你的操作系统文档。
注意,如果你运行tcsh
外壳,ulimit
将不工作!当你请求当前限制时,tcsh
也将报告不正确的值!在这种情况下,你应该用sh
启动safe_mysqld
!
18.12 使用DATE
列的问题
一个DATE
值的格式是'YYYY-MM-DD'
。根据ANSI SQL,不允许其他格式。你应该在UPDATE
表达式和SELECT
语句的WHERE子句中使用这个格式。例如:
mysql> SELECT * FROM tbl_name WHERE date >= '1997-05-05';
为了方便,如果日期用在数字上下文,MySQL自动变换一个日期到一个数字(并且反过来也如此)。当更新时和将一个日期与TIMESTAMP
、DATE
或DATETIME
列比较的一个WHERE
子句中,也是足够灵活以允许一种“宽松”的字符串格式。(宽松格式意味着任何标点字符用作在部件之间的分割符。例如,'1998-08-15'
和'1998#08#15'
是等价的。)MySQL也能变换不包含分割符的一个字符串(例如 '19980815'
),如果它作为一个日期说得通。
特殊日期'0000-00-00'
可以作为'0000-00-00'
被存储和检索。
当通过MyODBC使用一个'0000-00-00'
日期时,在MyODBC 2.50.12和以上版本,它将自动被转换为NULL
,因为ODBC不能处理这种日期。
因为MySQL实行了上述的变换,下列语句可以工作:
mysql> INSERT INTO tbl_name (idate) VALUES (19970505);
mysql> INSERT INTO tbl_name (idate) VALUES ('19970505');
mysql> INSERT INTO tbl_name (idate) VALUES ('97-05-05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997.05.05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997 05 05');
mysql> INSERT INTO tbl_name (idate) VALUES ('0000-00-00');
mysql> SELECT idate FROM tbl_name WHERE idate >= '1997-05-05';
mysql> SELECT idate FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT mod(idate,100) FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT idate FROM tbl_name WHERE idate >= '19970505';
然而,下列将不工作:
mysql> SELECT idate FROM tbl_name WHERE STRCMP(idate,'19970505')=0;
STRCMP()
是字符串函数,因此它将idate
转换为一个字符串并且实施字符串比较。它不将'19970505'
转换为一个日期并实施日期比较。
注意,MySQL不检查日期是否正确。如果你存储一个不正确的日期,例如'1998-2-31'
,错误的日期将被存储。如果日期不能被变换到任何合理的值,在DATE
字段中存储一个0
。这主要是一个速度问题并且我们认为检查日期是应用程序的责任,而不服务器。
18.13 时区问题
如果你有一个问题,SELECT NOW()
以GMT时间返回值而不是你的本地时间,你必须设定TZ
环境变量为你的当前时区。这应该在服务器运行的环境进行,例如在safe_mysqld
或mysql.server
中。
18.14 在搜索中的大小写敏感性
缺省地,MySQL搜索是大小写不敏感的(尽管有一些字符集从来不是忽略大小写的,例如捷克语
)。这意味着,如果你用col_name LIKE 'a%'
搜寻,你将得到所有以A
或a
开始的列值。如果你想要使这个搜索大小写敏感,使用象INDEX(col_name, "A")=0
检查一个前缀。或如果列值必须确切是"A"
,使用STRCMP(col_name, "A") = 0
。
简单的比较操作(>=、>、= 、< 、<=
、排序和聚合)是基于每个字符的“排序值”。有同样排序值的字符(象E,e和'e)被视为相同的字符!
LIKE
比较在每个字符的大写值上进行(E==e 但是E<>'e)。
如果你想要一个列总是被当作大小写敏感的方式,声明它为BINARY
。见7.7 CREATE TABLE
句法。
如果你使用以所谓的big5编码的中文数据,你要使所有的字符列是BINARY
,它可行,是因为big5编码字符的排序顺序基于 ASCII代码的顺序。
18.15 NULL
值问题
NULL
值的概念是造成SQL的新手的混淆的普遍原因,他们经常认为NULL
是和一个空字符串''
的一样的东西。不是这样的!例如,下列语句是完全不同的:
mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ("");
两个语句把值插入到phone
列,但是第一个插入一个NULL
值而第二个插入一个空字符串。第一个的含义可以认为是“电话号码不知道”,而第二个则可意味着“她没有电话”。
在SQL中,NULL
值在于任何其他值甚至NULL
值比较时总是假的(FALSE)。包含NULL
的一个表达式总是产生一个NULL
值,除非在包含在表达式中的运算符和函数的文档中指出。在下列例子,所有的列返回NULL
:
mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);
如果你想要寻找值是NULL
的列,你不能使用=NULL
测试。下列语句不返回任何行,因为对任何表达式,expr = NULL
是假的:
mysql> SELECT * FROM my_table WHERE phone = NULL;
要想寻找NULL
值,你必须使用IS NULL
测试。下例显示如何找出NULL
电话号码和空的电话号码:
mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = "";
在MySQL中,就像很多其他的SQL服务器一样,你不能索引可以有NULL
值的列。你必须声明这样的列为NOT NULL
,而且,你不能插入NULL
到索引的列中。
当用LOAD DATA INFILE
读取数据时,空列用''
更新。如果你想要在一个列中有NULL
值,你应该在文本文件中使用\N
。字面上的词'NULL'
也可以在某些情形下使用。见7.16 LOAD DATA INFILE
句法。
当使用ORDER BY
时,首先呈现NULL
值。如果你用DESC
以降序排序,NULL
值最后显示。当使用GROUP BY
时,所有的NULL
值被认为是相等的。
为了有助于NULL
的处理,你能使用IS NULL
和IS NOT NULL
运算符和IFNULL()
函数。
对某些列类型,NULL
值被特殊地处理。如果你将NULL
插入表的第一个TIMESTAMP
列,则插入当前的日期和时间。如果你将NULL
插入一个AUTO_INCREMENT
列,则插入顺序中的下一个数字。
18.16 alias
问题
你可以在GROUP BY
、ORDER BY
或在HAVING
部分中使用别名引用列。别名也可以用来为列取一个更好点的名字:
SELECT SQRT(a*b) as rt FROM table_name GROUP BY rt HAVING rt > 0;
SELECT id,COUNT(*) AS cnt FROM table_name GROUP BY id HAVING cnt > 0;
SELECT id AS "Customer identity" FROM table_name;
注意,你的 ANSI SQL 不允许你在一个WHERE
子句中引用一个别名。这是因为在WHERE
代码被执行时,列值还可能没有终结。例如下列查询是不合法:
SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id;
WHERE
语句被执行以确定哪些行应该包括GROUP BY
部分中,而HAVING
用来决定应该只用结果集合中的哪些行。
18.17 从关联的表中删除行
因为MySQL不支持子选择或在DELETE
语句中使用多个表,你应该使用下列方法从2个关联的表中删除行:
- 在主表中基于某个
WHERE
条件SELECT
行。
- 在主表中基于相同的条件
DELETE
行。
DELETE FROM related_table WHERE related_column IN (selected_rows)
如果在related_column
查询中的字符的全部数量超过1,048,576(缺省值max_allowed_packet
),你应该分成更小的部分并且执行多个DELETE
语句。如果related_column
是一个索引,你每次只删除100-1000个related_column
id将可能使得DELETE
最快。如果related_column
不是一个索引,速度与IN
子句中参数的数量无关。
18.18 解决没有匹配行的问题
如果你有一个复杂的查询,涉及多个表,但没有返回任何行,你应该使用下列过程查找你的询问有什么不对:
EXPLAIN
测试查询并且检查你是否能找出显然是错误的一些东西。见7.22 EXPLAIN
句法(得到关于一个SELECT
的信息)。
- 仅选择那些在
WHERE
子句中使用的字段。
- 一次从查询中删除一个表,直到它返回一些行。如果表很大,对查询使用
LIMIT 10
是一个好主意。
- 对应该已经匹配一行的列做一个
SELECT
,针对从询问中做后被删除的表。
- 如果你将
FLOAT
或DOUBLE
列与有小数的数字进行比较,你不能使用=
!。这个问题在大多数计算机语言是常见的,因为浮点值不是准确的值。mysql> SELECT * FROM table_name WHERE float_column=3.5;
->
mysql> SELECT * FROM table_name WHERE float_column between 3.45 and 3.55;
在大多数情况下,将FLOAT
改成一个DOUBLE
将修正它!
- 如果你仍然不能发现错误是什么,创建一个最小的可运行
mysql test < query.sql
的测试来显示你的问题。你可以用mysqldump --quick database tables > query.sql
创建一个测试文件,在一个编辑器编辑文件,删除一些插入行(如果有太多这些语句)并且在文件末尾加入你的选择语句。测试你仍然有问题,可以这样做:shell> mysqladmin create test2
shell> mysql test2 < query.sql
使用mysqlbug
的邮寄测试文件到[email]mysql@lists.mysql.com[/email]。
18.19 与ALTER TABLE
有关的问题
如果ALTER TABLE
死于这样一个错误:
Error on rename of './database/name.frm' to './database/B-a.frm' (Errcode: 17)
问题可能是MySQL在前一个ALTER TABLE
中已经崩溃并且留下了一个名为“A-xxx”或“B-xxx”的老的数据库表。在这种情况下,到MySQL数据目录中并删除所有名字以A-
或B-
开始的文件。(你可以把他们移到别的地方而不是删除他们)。
ALTER TABLE
工作方式是:
- 以要求的改变创建一个名为“A-xxx”的新表。
- 从老表把所有行拷贝到“A-xxx”。
- 老表被改名为“B-xxx”。
- “A-xxx”被改名为你的老表的名字。
- “B-xxx”被删除。
如果某些改名操作出错,MySQL试图还原改变。如果出错严重(当然,这不应该发生。),MySQL可能留下了老表为“B-xxx”但是一个简单改名就应该恢复你的数据。
18.20 怎样改变一张表中列的顺序
SQL的要点是中抽象应用程序以避免数据存储格式。你应该总是以你想要检索数据的意愿指定顺序。例如:
SELECT col_name1, col_name2, col_name3 FROM tbl_name;
将以col_name1
、col_name2
、col_name3
的顺序返回列,而:
SELECT col_name1, col_name3, col_name2 FROM tbl_name;
将以col_name1
、col_name3
、col_name2
的顺序返回列。
在一个应用程序中,你应该决不基于他们的位置使用SELECT *
检索列,因为被返回的列的顺序永远不能保证;对你的数据库的一个简单改变可能导致你的应用程序相当有戏剧性地失败。
不管怎样,如果你想要改变列的顺序,你可以这样做:
- 以正确的列顺序创建一张新表。
- 执行
INSERT INTO new_table SELECT fields-in-new_table-order FROM old_table
.
- 删除或改名
old_table
。
ALTER TABLE new_table RENAME old_table