分类: Mysql/postgreSQL
2008-05-14 11:24:08
UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1,col_name2=expr2,... [WHERE where_definition] [LIMIT #]
UPDATE
用新值更新现存表中行的列,SET
子句指出哪个列要修改和他们应该被给定的值,WHERE
子句,如果给出,指定哪个行应该被更新,否则所有行被更新。
如果你指定关键词LOW_PRIORITY
,执行UPDATE
被推迟到没有其他客户正在读取表时。
如果你从一个表达式的tbl_name
存取列,UPDATE
使用列的当前值。例如,下列语句设置age
为它的当前值加1:
mysql> UPDATE persondata SET age=age+1;
UPDATE
赋值是从左到右计算。例如,下列语句两倍age
列,然后加1:
mysql> UPDATE persondata SET age=age*2, age=age+1;
如果你设置列为其它当前有的值,MySQL注意到这点并且不更新它。
UPDATE
返回实际上被改变的行的数量。在MySQL 3.22或以后版本中,C API函数mysql_info()
返回被匹配并且更新的行数和在UPDATE
期间发生警告的数量。
在MySQL3.23中,你可使用LIMIT #
来保证只有一个给定数量的行被改变。
USE db_name
USE db_name
语句告诉MySQL使用db_name
数据库作为随后的查询的缺省数据库。数据库保持到会话结束,或发出另外一个USE
语句:
mysql> USE db1; mysql> SELECT count(*) FROM mytable; # selects from db1.mytable mysql> USE db2; mysql> SELECT count(*) FROM mytable; # selects from db2.mytable
利用USE
语句使得一个特定的数据库称为当前数据库并不阻止你访问在另外的数据库中的表。下面的例子访问db1
数据库中的author
表和db2
数据库中的editor
表:
mysql> USE db1; mysql> SELECT author_name,editor_name FROM author,db2.editor WHERE author.editor_id = db2.editor.editor_id;
USE
语句提供了Sybase的兼容性。
FLUSH flush_option [,flush_option]
如果你想要清除一些MySQL使用内部缓存,你应该使用FLUSH
命令。为了执行FLUSH
,你必须有reload权限。
flush_option
可以是下列任何东西:
HOSTS |
清空主机缓存表。如果你的某些主机改变IP数字,或如果你得到错误消息Host ... is blocked ,你应该清空主机表。当在连接MySQL服务器时,对一台给定的主机有多于max_connect_errors 个错误连续不断地发生,MySQL认定某些东西错了并且阻止主机进一步的连接请求。清空主机表允许主机再尝试连接。见)。你可用-O max_connection_errors=999999999 启动mysqld 来避免这条错误消息。 |
LOGS |
关闭并且再打开标准和更新记录文件。如果你指定了一个没有扩展名的更新记录文件,新的更新记录文件的扩展数字将相对先前的文件加1。 |
PRIVILEGES |
从mysql 数据库授权表中重新装载权限。 |
TABLES |
关闭所有打开的表。 |
STATUS |
重置大多数状态变量到0。 |
你也可以用mysqladmin
实用程序,使用flush-hosts
, flush-logs
, reload
或flush-tables
命令来访问上述的每一个命令。
KILL thread_id
每个对mysqld
的连接以一个单独的线程运行。你可以用看SHOW PROCESSLIST
命令察看哪个线程正在运行,并且用KILL thread_id
命令杀死一个线程。
如果你有process权限,你能看到并且杀死所有线程。否则,你只能看到并且杀死你自己的线程。
你也可以使用mysqladmin processlist
和mysqladmin kill
命令检查并杀死线程。
SHOW DATABASES [LIKE wild] or SHOW TABLES [FROM db_name] [LIKE wild] or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild] or SHOW INDEX FROM tbl_name [FROM db_name] or SHOW STATUS or SHOW VARIABLES [LIKE wild] or SHOW [FULL] PROCESSLIST or SHOW TABLE STATUS [FROM db_name] [LIKE wild] or SHOW GRANTS FOR user
SHOW
提供关于数据库、桌子、列或服务器的信息。如果使用LIKE wild
部分,wild
字符串可以是一个使用SQL的“%”和“_”通配符的字符串。
你能使用db_name.tbl_name
作为tbl_name FROM db_name
句法的另一种选择。这两个语句是相等的:
mysql> SHOW INDEX FROM mytable FROM mydb; mysql> SHOW INDEX FROM mydb.mytable;
SHOW DATABASES
列出在MySQL服务器主机上的数据库。你也可以用mysqlshow
命令得到这张表。
SHOW TABLES
列出在一个给定的数据库中的表。你也可以用mysqlshow db_name
命令得到这张表。
注意:如果一个用户没有一个表的任何权限,表将不在SHOW TABLES
或mysqlshow db_name
中的输出中显示。
SHOW COLUMNS
列出在一个给定表中的列。如果列类型不同于你期望的是基于CREATE TABLE
语句的那样,注意,MySQL有时改变列类型。见。
DESCRIBE
语句提供了类似SHOW COLUMNS
的信息。见。
SHOW TABLE STATUS
(在版本3.23引入)运行类似SHOW STATUS
,但是提供每个表的更多信息。你也可以使用mysqlshow --status db_name
命令得到这张表。下面的列被返回:
列 | 含义 |
Name |
表名 |
Type |
表的类型 (ISAM,MyISAM或HEAP) |
Row_format |
行存储格式 (固定, 动态, 或压缩) |
Rows |
行数量 |
Avg_row_length |
平均行长度 |
Data_length |
数据文件的长度 |
Max_data_length |
数据文件的最大长度 |
Index_length |
索引文件的长度 |
Data_free |
已分配但未使用了字节数 |
Auto_increment |
下一个 autoincrement(自动加1)值 |
Create_time |
表被创造的时间 |
Update_time |
数据文件最后更新的时间 |
Check_time |
最后对表运行一个检查的时间 |
Create_options |
与CREATE TABLE 一起使用的额外选项 |
Comment |
当创造表时,使用的注释 (或为什么MySQL不能存取表信息的一些信息)。 |
SHOW FIELDS
是SHOW COLUMNS
一个同义词,SHOW KEYS
是SHOW INDEX
一个同义词。你也可以用mysqlshow db_name tbl_name
或mysqlshow -k db_name tbl_name
列出一张表的列或索引。
SHOW INDEX
以非常相似于ODBC的SQLStatistics
调用的格式返回索引信息。下面的列被返回:
列 | 含义 |
Table |
表名 |
Non_unique |
0,如果索引不能包含重复。 |
Key_name |
索引名 |
Seq_in_index |
索引中的列顺序号, 从 1 开始。 |
Column_name |
列名。 |
Collation |
列怎样在索引中被排序。在MySQL中,这可以有值A (升序) 或NULL (不排序)。 |
Cardinality |
索引中唯一值的数量。这可通过运行isamchk -a 更改. |
Sub_part |
如果列只是部分被索引,索引字符的数量。NULL ,如果整个键被索引。 |
SHOW STATUS
提供服务器的状态信息(象mysqladmin extended-status
一样)。输出类似于下面的显示,尽管格式和数字可以有点不同:
+--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Connections | 17 | | Created_tmp_tables | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 2 | | Handler_delete | 2 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 0 | | Handler_read_rnd | 35 | | Handler_update | 0 | | Handler_write | 2 | | Key_blocks_used | 0 | | Key_read_requests | 0 | | Key_reads | 0 | | Key_write_requests | 0 | | Key_writes | 0 | | Max_used_connections | 1 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 1 | | Open_files | 2 | | Open_streams | 0 | | Opened_tables | 11 | | Questions | 14 | | Slow_queries | 0 | | Threads_connected | 1 | | Threads_running | 1 | | Uptime | 149111 | +--------------------------+--------+
上面列出的状态变量有下列含义:
Aborted_clients |
由于客户没有正确关闭连接已经死掉,已经放弃的连接数量。 |
Aborted_connects |
尝试已经失败的MySQL服务器的连接的次数。 |
Connections |
试图连接MySQL服务器的次数。 |
Created_tmp_tables |
当执行语句时,已经被创造了的隐含临时表的数量。 |
Delayed_insert_threads |
正在使用的延迟插入处理器线程的数量。 |
Delayed_writes |
用INSERT DELAYED 写入的行数。 |
Delayed_errors |
用INSERT DELAYED 写入的发生某些错误(可能重复键值 )的行数。 |
Flush_commands |
执行FLUSH 命令的次数。 |
Handler_delete |
请求从一张表中删除行的次数。 |
Handler_read_first |
请求读入表中第一行的次数。 |
Handler_read_key |
请求数字基于键读行。 |
Handler_read_next |
请求读入基于一个键的一行的次数。 |
Handler_read_rnd |
请求读入基于一个固定位置的一行的次数。 |
Handler_update |
请求更新表中一行的次数。 |
Handler_write |
请求向表中插入一行的次数。 |
Key_blocks_used |
用于关键字缓存的块的数量。 |
Key_read_requests |
请求从缓存读入一个键值的次数。 |
Key_reads |
从磁盘物理读入一个键值的次数。 |
Key_write_requests |
请求将一个关键字块写入缓存次数。 |
Key_writes |
将一个键值块物理写入磁盘的次数。 |
Max_used_connections |
同时使用的连接的最大数目。 |
Not_flushed_key_blocks |
在键缓存中已经改变但是还没被清空到磁盘上的键块。 |
Not_flushed_delayed_rows |
在INSERT DELAY 队列中等待写入的行的数量。 |
Open_tables |
打开表的数量。 |
Open_files |
打开文件的数量。 |
Open_streams |
打开流的数量(主要用于日志记载) |
Opened_tables |
已经打开的表的数量。 |
Questions |
发往服务器的查询的数量。 |
Slow_queries |
要花超过long_query_time 时间的查询数量。 |
Threads_connected |
当前打开的连接的数量。 |
Threads_running |
不在睡眠的线程数量。 |
Uptime |
服务器工作了多少秒。 |
关于上面的一些注释:
Opened_tables
太大,那么你的table_cache
变量可能太小。
key_reads
太大,那么你的key_cache
可能太小。缓存命中率可以用key_reads
/key_read_requests
计算。
Handler_read_rnd
太大,那么你很可能有大量的查询需要MySQL扫描整个表或你有没正确使用键值的联结(join)。 SHOW VARIABLES
显示出一些MySQL系统变量的值,你也能使用mysqladmin variables
命令得到这个信息。如果缺省值不合适,你能在mysqld
启动时使用命令行选项来设置这些变量的大多数。输出类似于下面的显示,尽管格式和数字可以有点不同:
+------------------------+--------------------------+ | Variable_name | Value | +------------------------+--------------------------+ | back_log | 5 | | connect_timeout | 5 | | basedir | /my/monty/ | | datadir | /my/monty/data/ | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | join_buffer_size | 131072 | | flush_time | 0 | | interactive_timeout | 28800 | | key_buffer_size | 1048540 | | language | /my/monty/share/english/ | | log | OFF | | log_update | OFF | | long_query_time | 10 | | low_priority_updates | OFF | | max_allowed_packet | 1048576 | | max_connections | 100 | | max_connect_errors | 10 | | max_delayed_threads | 20 | | max_heap_table_size | 16777216 | | max_join_size | 4294967295 | | max_sort_length | 1024 | | max_tmp_tables | 32 | | net_buffer_length | 16384 | | port | 3306 | | protocol-version | 10 | | record_buffer | 131072 | | skip_locking | ON | | socket | /tmp/mysql.sock | | sort_buffer | 2097116 | | table_cache | 64 | | thread_stack | 131072 | | tmp_table_size | 1048576 | | tmpdir | /machine/tmp/ | | version | 3.23.0-alpha-debug | | wait_timeout | 28800 | +------------------------+--------------------------+
见。
SHOW PROCESSLIST
显示哪个线程正在运行,你也能使用mysqladmin processlist
命令得到这个信息。如果你有process权限, 你能看见所有的线程,否则,你仅能看见你自己的线程。见。如果你不使用FULL
选项,那么每个查询只有头100字符被显示出来。
SHOW GRANTS FOR user
列出对一个用户必须发出以重复授权的授权命令。
mysql> SHOW GRANTS FOR root@localhost; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root''localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+
EXPLAIN tbl_name or EXPLAIN SELECT select_options
EXPLAIN tbl_name
是DESCRIBE tbl_name
或SHOW COLUMNS FROM tbl_name
的一个同义词。
当你在一条SELECT
语句前放上关键词EXPLAIN
,MySQL解释它将如何处理SELECT
,提供有关表如何联结和以什么次序联结的信息。
借助于EXPLAIN
,你可以知道你什么时候必须为表加入索引以得到一个使用索引找到记录的更快的SELECT
。你也能知道优化器是否以一个最佳次序联结表。为了强制优化器对一个SELECT
语句使用一个特定联结次序,增加一个STRAIGHT_JOIN
子句。
对于非简单的联结,EXPLAIN
为用于SELECT
语句中的每个表返回一行信息。表以他们将被读入的顺序被列出。MySQL用一边扫描多次联结的方式解决所有联结,这意味着MySQL从第一个表中读一行,然后找到在第二个表中的一个匹配行,然后在第3个表中等等。当所有的表被处理完,它输出选择的列并且回溯表列表直到找到一个表有更多的匹配行,从该表读入下一行并继续处理下一个表。
从EXPLAIN
的输出包括下面列:
table
type
possible_keys
possible_keys
列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于表的次序。这意味着在possible_keys中的某些键实际上不能以生成的表次序使用。如果该列是空的,没有相关的索引。在这种情况下,你也许能通过检验WHERE
子句看是否它引用某些列或列不是适合索引来提高你的查询性能。如果是这样,创造一个适当的索引并且在用EXPLAIN
检查查询。见。为了看清一张表有什么索引,使用SHOW INDEX FROM tbl_name
。
key
key
列显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL
。
key_len
key_len
列显示MySQL决定使用的键长度。如果键是NULL
,长度是NULL
。注意这告诉我们MySQL将实际使用一个多部键值的几个部分。
ref
ref
列显示哪个列或常数与key
一起用于从表中选择行。
rows
rows
列显示MySQL相信它必须检验以执行查询的行数。
Extra
Extra
列包括文字Only index
,这意味着信息只用索引树中的信息检索出的。通常,这比扫描整个表要快。如果Extra
列包括文字where used
,它意味着一个WHERE
子句将被用来限制哪些行与下一个表匹配或发向客户。 不同的联结类型列在下面,以最好到最差类型的次序:
system
const
联结类型的一个特例。
const
const
表很快,因为它们只读取一次!
eq_ref
const
类型。它用在一个索引的所有部分被联结使用并且索引是UNIQUE
或PRIMARY KEY
。
ref
UNIQUE
或PRIMARY KEY
(换句话说,如果联结不能基于键值选择单个行的话),使用ref
。如果被使用的键仅仅匹配一些行,该联结类型是不错的。
range
ref
列显示哪个索引被使用。
index
ALL
相同,除了只有索引树被扫描。这通常比ALL
快,因为索引文件通常比数据文件小。
ALL
const
的表,这通常不好,并且通常在所有的其他情况下很差。你通常可以通过增加更多的索引来避免ALL
,使得行能从早先的表中基于常数值或列值被检索出。 通过相乘EXPLAIN
输出的rows
行的所有值,你能得到一个关于一个联结要多好的提示。这应该粗略地告诉你MySQL必须检验多少行以执行查询。当你使用max_join_size
变量限制查询时,也用这个数字。见。
下列例子显示出一个JOIN
如何能使用EXPLAIN
提供的信息逐步被优化。
假定你有显示在下面的SELECT
语句,你使用EXPLAIN
检验:
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, tt.ProjectReference, tt.EstimatedShipDate, tt.ActualShipDate, tt.ClientID, tt.ServiceCodes, tt.RepetitiveID, tt.CurrentProcess, tt.CurrentDPPerson, tt.RecordVolume, tt.DPPrinted, et.COUNTRY, et_1.COUNTRY, do.CUSTNAME FROM tt, et, et AS et_1, do WHERE tt.SubmitTime IS NULL AND tt.ActualPC = et.EMPLOYID AND tt.AssignedPC = et_1.EMPLOYID AND tt.ClientID = do.CUSTNMBR;
对于这个例子,假定:
表 | 列 | 列类型 |
tt |
ActualPC |
CHAR(10) |
tt |
AssignedPC |
CHAR(10) |
tt |
ClientID |
CHAR(10) |
et |
EMPLOYID |
CHAR(15) |
do |
CUSTNMBR |
CHAR(15) |
表 | 索引 |
tt |
ActualPC |
tt |
AssignedPC |
tt |
ClientID |
et |
EMPLOYID (主键) |
do |
CUSTNMBR (主键) |
tt.ActualPC
值不是均匀分布的。 开始,在任何优化被施行前,EXPLAIN
语句产生下列信息:
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 do ALL PRIMARY NULL NULL NULL 2135 et_1 ALL PRIMARY NULL NULL NULL 74 tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 range checked for each record (key map: 35)
因为type
对每张表是ALL
,这个输出显示MySQL正在对所有表进行一个完整联结!这将花相当长的时间,因为必须检验每张表的行数的乘积次数!对于一个实例,这是74 * 2135 * 74 * 3872 = 45,268,558,720
行。如果表更大,你只能想象它将花多长时间……
如果列声明不同,这里的一个问题是MySQL(还)不能高效地在列上使用索引。在本文中,VARCHAR
和CHAR
是相同的,除非他们声明为不同的长度。因为tt.ActualPC
被声明为CHAR(10)
并且et.EMPLOYID
被声明为CHAR(15)
,有一个长度失配。
为了修正在列长度上的不同,使用ALTER TABLE
将ActualPC
的长度从10个字符变为15个字符:
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
现在tt.ActualPC
和et.EMPLOYID
都是VARCHAR(15)
,再执行EXPLAIN
语句产生这个结果:
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used do ALL PRIMARY NULL NULL NULL 2135 range checked for each record (key map: 1) et_1 ALL PRIMARY NULL NULL NULL 74 range checked for each record (key map: 1) et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
这不是完美的,但是是好一些了(rows
值的乘积少了一个74一个因子),这个版本在几秒内执行。
第2种改变能消除tt.AssignedPC = et_1.EMPLOYID
和tt.ClientID = do.CUSTNMBR
比较的列的长度失配:
mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15), MODIFY ClientID VARCHAR(15);
现在EXPLAIN
产生的输出显示在下面:
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 tt ref AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
这“几乎”象它能得到的一样好。
剩下的问题是,缺省地,MySQL假设在tt.ActualPC
列的值是均匀分布的,并且对tt
表不是这样。幸好,很容易告诉MySQL关于这些:
shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt shell> mysqladmin refresh
现在联结是“完美”的了,而且EXPLAIN
产生这个结果:
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1 et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
注意在从EXPLAIN
输出的rows
列是一个来自MySQL联结优化器的“教育猜测”;为了优化查询,你应该检查数字是否接近事实。如果不是,你可以通过在你的SELECT
语句里面使用STRAIGHT_JOIN
并且试着在在FROM
子句以不同的次序列出表,可能得到更好的性能。
{DESCRIBE | DESC} tbl_name {col_name | wild}
DESCRIBE
提供关于一张表的列的信息。col_name
可以是一个列名字或包含SQL的“%”和“_”通配符的一个字符串。
如果列类型不同于你期望的是基于一个CREATE TABLE
语句,注意MySQL有时改变列类型。见。
这个语句为了与 Oracle 兼容而提供的。
SHOW
语句提供类似的信息。见。
LOCK TABLES tbl_name [AS alias] {READ | [LOW_PRIORITY] WRITE} [, tbl_name {READ | [LOW_PRIORITY] WRITE} ...] ... UNLOCK TABLES
LOCK TABLES
为当前线程锁定表。UNLOCK TABLES
释放被当前线程持有的任何锁。当线程发出另外一个LOCK TABLES
时,或当服务器的连接被关闭时,当前线程锁定的所有表自动被解锁。
如果一个线程获得在一个表上的一个READ
锁,该线程(和所有其他线程)只能从表中读。如果一个线程获得一个表上的一个WRITE
锁,那么只有持锁的线程READ
或WRITE
表,其他线程被阻止。
每个线程等待(没有超时)直到它获得它请求的所有锁。
WRITE
锁通常比READ
锁有更高的优先级,以确保更改尽快被处理。这意味着,如果一个线程获得READ
锁,并且然后另外一个线程请求一个WRITE
锁, 随后的READ
锁请求将等待直到WRITE
线程得到了锁并且释放了它。当线程正在等待WRITE
锁时,你可以使用LOW_PRIORITY WRITE
允许其他线程获得READ
锁。如果你肯定终于有个时刻没有线程将有一个READ
锁,你应该只使用LOW_PRIORITY WRITE
。
当你使用LOCK TABLES
时,你必须锁定你将使用的所有表!如果你正在一个查询中多次使用一张表(用别名),你必须对每个别名得到一把锁!这条政策保证表锁定不会死锁。
注意你应该不锁定任何你正在用INSERT DELAYED
使用的表,这是因为在这种情况下,INSERT
被一个不同的线程执行。
通常,你不必锁定表,因为所有单个UPDATE
语句是原语;没有其他线程能防碍任何其它正在执行SQL语句的线程。当你想锁定表,有一些情况:
READ
锁定的表并且没有其他线程能读一个WRITE
-锁定的表。
SELECT
和一个UPDATE
之间没有其他线程到来,你必须使用LOCK TABLES
。下面显示的例子要求LOCK TABLES
以便安全地执行: mysql> LOCK TABLES trans READ, customer WRITE; mysql> select sum(value) from trans where customer_id= some_id; mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id; mysql> UNLOCK TABLES;
没有LOCK TABLES
,另外一个线程可能有一个机会在执行SELECT
和UPDATE
语句之间往trans
表中插入一个新行。
通过使用渐增更改(UPDATE customer SET value=value+new_value
)或LAST_INSERT_ID()
函数,在很多情况下你能使用LOCK TABLES
来避免。
你也可以使用用户级锁定函数GET_LOCK()
和RELEASE_LOCK()
解决一些情况,这些锁保存在服务器的一张哈希表中并且用pthread_mutex_lock()
和pthread_mutex_unlock()
实现以获得高速度。见。
有关锁定政策的更多信息,见。
SET [OPTION] SQL_VALUE_OPTION= value, ...
SET OPTION
设置影响服务器或你的客户操作的各种选项。你设置的任何选择保持有效直到当前会话结束,或直到你设置选项为不同的值。
CHARACTER SET character_set_name | DEFAULT
character_set_name
当前唯一的选项是 cp1251_koi8
,但是你能容易通过编辑在MySQL源代码分发的“sql/convert.cc”文件增加新的映射。缺省映射能用character_set_name
的DEFAULT
值恢复。注意设置CHARACTER SET
选项的语法不同于设置其他选项目的语法。
PASSWORD = PASSWORD('some password')
PASSWORD FOR user = PASSWORD('some password')
mysql
数据库的用户可以这样做。用户应该以user@hostname
格式给出,这里user
和hostname
完全与他们列在mysql.user
表条目的User
和Host
列一样。例如,如果你有一个条目其User
和Host
字段是'bob'
和'%.loc.gov'
,你将写成: mysql> SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass"); 或 mysql> UPDATE mysql.user SET password=PASSWORD("newpass") where user="bob' and host="%.loc.gov";
SQL_AUTO_IS_NULL = 0 | 1
1
(缺省 ),那么对于一个具有一个自动加1的行的表,用下列构件能找出最后插入的行:WHERE auto_increment_column IS NULL
。这被一些 ODBC 程序入Access使用。
SQL_BIG_TABLES = 0 | 1
1
,所有临时表存在在磁盘上而非内存中。这将更慢一些,但是对需要大的临时表的大SELECT
操作,你将不会得到The table tbl_name is full
的错误。对于一个新连接的缺省值是0
(即,使用内存中的临时表)。
SQL_BIG_SELECTS = 0 | 1
0
,如果一个SELECT
尝试可能花很长的时间,MySQL将放弃。这在一个不妥当的WHERE
语句发出时是有用的。一个大的查询被定义为一个将可能必须检验多于max_join_size
行的SELECT
。对一个新连接的缺省值是1
(它将允许所有SELECT
语句)。
SQL_LOW_PRIORITY_UPDATES = 0 | 1
1
,所有INSERT
、UPDATE
、DELETE
和LOCK TABLE WRITE
语句等待,直到在受影响的表上没有未解决的SELECT
或LOCK TABLE READ
。
SQL_SELECT_LIMIT = value | DEFAULT
SELECT
语句返回的记录的最大数量。如果一个SELECT
有一个LIMIT
子句,LIMIT
优先与SQL_SELECT_LIMIT
值。对一个新连接的缺省值是“无限”的。如果你改变了限制,缺省值能用SQL_SELECT_LIMIT
的一个DEFAULT
值恢复。
SQL_LOG_OFF = 0 | 1
1
,如果客户有process权限,对该客户没有日志记载到标准的日志文件中。这不影响更新日志记录!
SQL_LOG_UPDATE = 0 | 1
0
, 如果客户有process权限,对该客户没有日志记载到更新日志中。这不影响标准日志文件!
TIMESTAMP = timestamp_value | DEFAULT
LAST_INSERT_ID = #
LAST_INSERT_ID()
返回的值。当你在更新一个表的命令中使用LAST_INSERT_ID()
时,它存储在更新日志中。
INSERT_ID = #
AUTO_INCREMENT
值时,由INSERT
命令使用的值。这主要与更新日志一起使用。 GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {tbl_name | * | *.* | db_name.*} TO user_name [IDENTIFIED BY 'password'] [, user_name [IDENTIFIED BY 'password'] ...] [WITH GRANT OPTION] REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {tbl_name | * | *.* | db_name.*} FROM user_name [, user_name ...]
GRANT
在MySQL 3.22.11或以后版本中实现。对于更早MySQL版本,GRANT
语句不做任何事情。
GRANT
和REVOKE
命令允许系统主管在4个权限级别上授权和撤回赋予MySQL用户的权利:
mysql.user
表中。
mysql.db
和mysql.host
表中。
mysql.tables_priv
表中。
mysql.columns_priv
表中。 对于GRANT
如何工作的例子,见。
对于GRANT
和REVOKE
语句,priv_type
可以指定下列的任何一个:
ALL PRIVILEGES FILE RELOAD ALTER INDEX SELECT CREATE INSERT SHUTDOWN DELETE PROCESS UPDATE DROP REFERENCES USAGE
ALL
是ALL PRIVILEGES
的一个同义词,REFERENCES
还没被实现,USAGE
当前是“没有权限”的一个同义词。它能用在你想要创建一个没有权限用户的时候。
为了从一个用户撤回grant的权限,使用GRANT OPTION
的一个priv_type
值:
REVOKE GRANT OPTION ON ... FROM ...;
对于表,你能指定的唯一priv_type
值是SELECT
、INSERT
、UPDATE
、DELETE
、CREATE
、DROP
、GRANT
、INDEX
和ALTER
。
对于列,你能指定的唯一priv_type
值是(即,当你使用一个column_list
子句时)是SELECT
、INSERT
和UPDATE
。
你能通过使用ON *.*
语法设置全局权限,你能通过使用ON db_name.*
语法设置数据库权限。如果你指定ON *
并且你有一个当前数据库,你将为该数据库设置权限。(警告:如果你指定ON *
而你没有一个当前数据库,你将影响全局权限!)
为了容纳对任意主机的用户授予的权利,MySQL支持以user@host
格式指定user_name
值。如果你想要指定一个特殊字符的一个user
字符串(例如“-”),或一个包含特殊字符或通配符的host
字符串(例如“%”),你可以用括号括起能用户或主机名字 (例如,'test-user'@'test-hostname'
)。
你能在主机名中指定通配符。例如,user@"%.loc.gov"
适用于在loc.gov
域中任何主机的user
,并且user@"144.155.166.%"
适用于在144.155.166
类 C 子网中任何主机的user
。
简单形式的user
是user@"%"
的一个同义词。注意:如果你允许匿名用户连接MySQL服务器(它是缺省的),你也应该增加所有本地用户如user@localhost
,因为否则,当用户试图从本地机器上登录到MySQL服务器时,对于mysql.user
表中的本地主机的匿名用户条目将被使用!匿名用户通过插入有User=''
的条目到mysql.user
表中来定义。通过执行这个查询,你可以检验它是否作用于你:
mysql> SELECT Host,User FROM mysql.user WHERE User='';
目前,GRANT
仅支持最长60个字符的主机、表、数据库和列名。一个用户名字能最多到16个字符。
对与一个表或列的权限是由4个权限级别的逻辑或形成的。例如,如果mysql.user
表指定一个用户有一个全局select权限,它不能被数据库、表或列的一个条目否认。
对于一个列的权限能如下计算:
global privileges OR (database privileges AND host privileges) OR table privileges OR column privileges
在大多数情况下,你只授予用户一个权限级别上的权限,因此现实通常不象上面所说的那样复杂。:) 权限检查过程的细节在中给出。
如果你为一个在mysql.user
表中不存在的用户/主机名组合授权,一个条目被增加并且保留直到用一个DELETE
命令删除。换句话说,GRANT
可以创建user
表的条目,但是REVOKE
将不删除;你必须明确地使用DELETE
删除.
在MySQL 3.22.12或以后,如果创建一个新用户或如果你有全局授予权限,用户的口令将被设置为由IDENTIFIED BY
子句指定的口令,如果给出一个。如果用户已经有了一个口令,它被一个新的代替。
警告:如果你创造一个新用户但是不指定一个IDENTIFIED BY
子句,用户没有口令。这是不安全的。
口令也能用SET PASSWORD
命令设置。见。
如果你为一个数据库授权,如果需要在mysql.db
表中创建一个条目。当所有为数据库的授权用REVOKE
删除时,这个条目被删除。
如果一个用户没有在一个表上的任何权限,当用户请求一系列表时,表不被显示(例如,用一个SHOW TABLES
语句)。
WITH GRANT OPTION
子句给与用户有授予其他用户在指定的权限水平上的任何权限的能力。你应该谨慎对待你授予他grant权限的用户,因为具有不同权限的两个用户也许能合并权限!
你不能授予其他用户你自己不具备的权限; agree权限允许你放弃你仅仅拥有的那些权限。
要知道,当你将一个特定权限级别上的grant授予其他用户,用户已经拥有(或在未来被授予!)的在该级别上的任何权限也可由该用户授权。假定你授权一个用户在一个数据库上的insert权限,那么如果你授权在数据库上select权限并且指定WITH GRANT OPTION
,用户能不仅放弃select权限,还有insert。如果你授权用户在数据库上的update权限,用户能放弃insert、select和update。
你不应该将alter权限授予一个一般用户。如果你这样做,用户可以通过重命名表试图颠覆权限系统!
注意,如果你正在使用即使一个用户的表或列的权限,服务器要检查所有用户的表和列权限并且这将使MySQL慢下来一点。
当mysqld
启动时,所有的权限被读入存储器。数据库、表和列权限马上生效,而用户级权限在下一次用户连接时生效。你用GRANT
或REVOKE
对受权表执行的更改立即被服务器知晓。如果你手工修改授权表(使用INSERT
、UPDATE等等),你应该执行一个FLUSH PRIVILEGES
语句或运行mysqladmin flush-privileges
告诉服务器再次装载授权表。见。
ANSI SQL版本的GRANT
与MySQL版本之间的最大差别:
REVOKE
命令或操作MySQL授权表抛弃。
CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name[(length)],... )
CREATE INDEX
语句在MySQL版本 3.22 以前不做任何事情。在 3.22 或以后版本中,CREATE INDEX
被映射到一个ALTER TABLE
语句来创建索引。见。
通常,你在用CREATE TABLE
创建表本身时创建表的所有索引。见。CREATE INDEX
允许你把索引加到现有表中。
一个(col1,col2,...)
形式的列表创造一个多列索引。索引值有给定列的值串联而成。
对于CHAR
和VARCHAR
列,索引可以只用一个列的部分来创建,使用col_name(length)
句法。(在BLOB
和TEXT
列上需要长度)。下面显示的语句使用name
列的头10个字符创建一个索引:
mysql> CREATE INDEX part_of_name ON customer (name(10));
因为大多数名字通常在头10个字符不同,这个索引应该不比从整个name
列的创建的索引慢多少。另外,在索引使用部分的列能使这个索引成为更小的文件大部分, 它能保存很多磁盘空格并且可能也加快INSERT
操作!
注意,如果你正在使用MySQL版本 3.23.2 或更新并且正在使用MyISAM
桌子类型,你只能在可以有NULL
值的列或一个BLOB
/TEXT
列上增加一个索引,
关于MySQL如何使用索引的更多信息,见。
DROP INDEX index_name ON tbl_name
DROP INDEX
从tbl_name
表抛弃名为index_name
的索引。DROP INDEX
在MySQL 3.22 以前的版本中不做任何事情。在 3.22 或以后,DROP INDEX
被映射到一个ALTER TABLE
语句来抛弃索引。见。
MySQL服务器支持# to end of line
、-- to end of line
和/* in-line or multiple-line */
注释风格:
mysql> select 1+1; # This comment continues to the end of line mysql> select 1+1; -- This comment continues to the end of line mysql> select 1 /* this is an in-line comment */ + 1; mysql> select 1+ /* this is a multiple-line comment */ 1;
注意--
注释风格要求你在--
以后至少有一个空格!
尽管服务者理解刚才描述的注释句法,mysql
客户分析/* ... */
注释的方式上有一些限制:
mysql
,你能告知有些混乱,因为提示符从mysql>
变为'>
或">
。
当你交互式运行mysql
时和当你把命令放在一个文件中并用mysql < some-file
告诉mysql
从那个文件读它的输入时,这些限制都适用。
MySQL不支持‘--’的ANSI SQL注释风格。见。
CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|REAL|INTEGER} SONAME shared_library_name DROP FUNCTION function_name
一个用户可定义函数(UDF)是用一个像MySQL的原生(内置)函数如ABS()
和CONCAT()
的新函数来扩展MySQL的方法。
AGGREGATE
是MySQL 3.23的一个新选项。一个AGGREGATE
函数功能就像一个原生MySQL GROUP
函数如SUM
或COUNT()
。
CREATE FUNCTION
在mysql.func
系统表中保存函数名、类型和共享库名。你必须对mysql
数据库有insert和delete权限以创建和抛弃函数。
所有活跃的函数在每次服务器启动时被重新装载,除非你使用--skip-grant-tables
选项启动mysqld
,在这种情况下,UDF初始化被跳过并且UDF是无法获得的。(一个活跃函数是一个已经用CREATE FUNCTION
装载并且没用DROP FUNCTION
删除的函数。)
关于编写用户可定义函数的指令,见。对于UDF的工作机制,函数必须用 C 或 C++ 编写,你的操作系统必须支持动态装载并且你必须动态编译了mysqld
(不是静态)。
一个常见的问题源自于试图使用MySQL内置的数据类型或函数名同名的列来创建数据库表,例如TIMESTAMP
或GROUP
。你允许这样做(例如,ABS
是一个允许的列名),但是当使用其名字也是列名的函数时,在一个函数名和“ ( ”之间不允许白空。
下列词明确地在MySQL中被保留。他们的大多数被 ANSI SQL92 禁止作为列或表名(例如,group
)。一些被保留因为MySQL需要他们并且正在(当前)使用一个yacc
分析器:
action |
add |
aggregate |
all |
alter |
after |
and |
as |
asc |
avg |
avg_row_length |
auto_increment |
between |
bigint |
bit |
binary |
blob |
bool |
both |
by |
cascade |
case |
char |
character |
change |
check |
checksum |
column |
columns |
comment |
constraint |
create |
cross |
current_date |
current_time |
current_timestamp |
data |
database |
databases |
date |
datetime |
day |
day_hour |
day_minute |
day_second |
dayofmonth |
dayofweek |
dayofyear |
dec |
decimal |
default |
delayed |
delay_key_write |
delete |
desc |
describe |
distinct |
distinctrow |
double |
drop |
end |
else |
escape |
escaped |
enclosed |
enum |
explain |
exists |
fields |
file |
first |
float |
float4 |
float8 |
flush |
foreign |
from |
for |
full |
function |
global |
grant |
grants |
group |
having |
heap |
high_priority |
hour |
hour_minute |
hour_second |
hosts |
identified |
ignore |
in |
index |
infile |
inner |
insert |
insert_id |
int |
integer |
interval |
int1 |
int2 |
int3 |
int4 |
int8 |
into |
if |
is |
isam |
join |
key |
keys |
kill |
last_insert_id |
leading |
left |
length |
like |
lines |
limit |
load |
local |
lock |
logs |
long |
longblob |
longtext |
low_priority |
max |
max_rows |
match |
mediumblob |
mediumtext |
mediumint |
middleint |
min_rows |
minute |
minute_second |
modify |
month |
monthname |
myisam |
natural |
numeric |
no |
not |
null |
on |
optimize |
option |
optionally |
or |
order |
outer |
outfile |
pack_keys |
partial |
password |
precision |
primary |
procedure |
process |
processlist |
privileges |
read |
real |
references |
reload |
regexp |
rename |
replace |
restrict |
returns |
revoke |
rlike |
row |
rows |
second |
select |
set |
show |
shutdown |
smallint |
soname |
sql_big_tables |
sql_big_selects |
sql_low_priority_updates |
sql_log_off |
sql_log_update |
sql_select_limit |
sql_small_result |
sql_big_result |
sql_warnings |
straight_join |
starting |
status |
string |
table |
tables |
temporary |
terminated |
text |
then |
time |
timestamp |
tinyblob |
tinytext |
tinyint |
trailing |
to |
type |
use |
using |
unique |
unlock |
unsigned |
update |
usage |
values |
varchar |
variables |
varying |
varbinary |
with |
write |
when |
where |
year |
year_month |
zerofill |
下列符号(来自上表)被ANSI SQL禁止但是被MySQL允许作为列/表名。这是因为这些名字的一些是很自然的名字并且很多人已经使用了他们。
ACTION
BIT
DATE
ENUM
NO
TEXT
TIME
TIMESTAMP