MySQL Cheat Sheet
JDBC配置
Data Source URL: jdbc:mysql://localhost:3306/cs
JDBC Driver Class: com.mysql.jdbc.Driver
注释
mysql 服务器支持
# 到该行结束
-- 到该行结束 -- (双长划) 注释风格要求在两个长划后至少有一个空格！
以及 /* 行中间或多个行 */ 的注释方格
单引号和双引号被用来标志一个被引用字符串的开始，即使是在一个注释中。如果注释中的引号没有另一个引号与之配对，那和语法分析程序就不会认为注释结束。如果你以交互式运行 mysql，你会产生困惑，因为提示符从 mysql> 变为 '> 或 ">。
BRIEF INTRODUCTION
MySQL服务器会将每个数据库映射到MySQL数据目录下的1个目录中，并将数据库中的表映射到数据库目录下的文件名如果操作系统的文件名区分大小写（如大多数Unix系统），当MySQL服务器运行在这类操作系统上时，数据库名和表名也区分大小写.
MySQL服务器不支持表空间。
删除表时，不自动取消关于表的权限。必须明确发出REVOKE语句，以撤销针对表的权限。
在完整性具有最高重要性的情况下，即使是对非事务性表，MySQL也能提供事务级别的可靠性和安全性。如果使用LOCK TABLES锁定了表，所有更新均将被暂时中止直至完整性检查完成。如果你获得了对某一表的READ LOCAL锁定（与写锁定相对），该表允许在表尾执行并行插入，当其他客户端执行插入操作时，允许执行读操作。新插入的记录不会被有读锁定属性的客户端看到，直至解除了该锁定为止。使用INSERT DELAYED，能够将插入项置于本地队列中，直至锁定解除，不会让客户端等待插入完成。
对于InnoDB之外的其他存储引擎，MySQL服务器能够解析CREATE TABLE语句中的FOREIGN KEY语法，但不能使用或保存它稍后，还将为MyISAM表实现外键约束
在MySQL中，0或 NULL意味着假而其它值意味着真。布尔运算的默认真值是1。
安装目录
使用MySQL AB's Linux RPM分发进行安装后，将在以下系统目录产生文件：
| 目录 | 目录内容 |
| /usr/bin | 客户端程序和脚本 |
| /usr/sbin | mysqld服务器 |
| /var/lib/mysql | 日志文件，数据库 |
| /usr/share/doc/packages | 文档 |
| /usr/include/mysql | 包含(头)文件 |
| /usr/lib/mysql | 库 |
| /usr/share/mysql | 错误消息和字符集文件 |
| /usr/share/sql-bench | 基准程序 |
在Unix中，可以在你选择的安装位置解压并安装tar文件二进制分发(typically /usr/local/mysql)并在该位置创建以下目录：
| 目录 | 目录内容 |
| bin | 客户端程序和mysqld服务器 |
| data | 日志文件，数据库 |
| docs | 文档，ChangeLog |
| include | 包含(头)文件 |
| lib | 库 |
| scripts | mysql_install_db |
| share/mysql | 错误消息文件 |
| sql-bench | 基准程序 |
配置并编译完源码分发后，便开始安装。默认情况下，可以将文件安装到/usr/local，即在下面的子目录中：
| 目录 | 目录内容 |
| bin | 客户端程序和脚本 |
| include/mysql | 包含(头)文件 |
| info | Info格式的文档 |
| lib/mysql | 库 |
| libexec | mysqld服务器 |
| share/mysql | 错误消息文件 |
| sql-bench | 基准程序和crash-me测试 |
| var | 数据库和日志文件 |
客户端中文显示SET NAMES gb2312
在批处理模式下使用mysql
mysql < batch-file
mysql -e "source batch-file"
mysql -h host -u user -p < batch-file
mysql < batch-file | more
mysql < batch-file > mysql.out
mysql [options] databasename < file.sql
This will execute all SQL commands for the database databasename contained in file.sql.
THREADS shows a list of all active MySQL threads (corresponds to the SQL command SHOWPROCESSLIST).
MySQL Query Browser
Ctrl+Return executes the current SQL command (like EXECUTE).
Ctrl+Shift+Return executes the command, but shows the result in a new dialog sheet. This has the advantage that the results of previous commands remain available.
F11 changes the arrangement of the individual windows and enlarges the input region for SQL commands. This makes it easier to enter long SQL commands. If you press F11 again, the standard layout is restored.
F12 closes the individual windows except for the current result region. This makes it easier to read extensive
results. Pressing F12 again restores the original window layout.
Data Types for Integer
Deleting Foreign Key Constraints
ALTER TABLE tablename DROP FOREIGN KEY foreign_key_id
Adding Foreign Key Constraints
ALTER TABLE tablename CONSTRAINT symbol FOREIGN KEY [id] (index_col_name, ...)= o ns = "urn:schemas-microsoft-com:office:office" />
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
EXPLAIN SELECT
- table shows us which table the output is about (for when you join many tables in the query)
- type is an important one - it tells us which type of join is being used. From best to worst the types are: system, const, eq_ref, ref, range, index, all
- possible_keys Shows which possible indexes apply to this table
- key And which one is actually used
- key_len give us the length of the key used. The shorter that better.
- ref Tells us which column, or a constant, is used
- rows Number of rows mysql believes it must examine to get the data
- extra Extra info - the bad ones to see here are "using temporary" and "using filesort"
Query and Index Optimization
MySQL will only ever use one index per table per query.
In making the decision as to which columns should be provided with indexes, one may sometimes obtain some assistance from the command EXPLAIN SELECT. This is simply an ordinary SELECT command prefixed with the key word EXPLAIN. Instead of SELECT being simply executed, MySQL places information in a table as to how the query was executed and which indexes (to the extent that they exist) came into play.
Unique indexes versus primary keys
In MyISAM tables, there's almost no difference. The only thing special about a primary key is that it can't contain NULL values. The primary key is simply a NOT NULL UNIQUE INDEX named PRIMARY. MyISAM tables don't require that you declare a primary key.
InnoDB and BDB tables require primary keys for every table. There's no requirement that you specify one, however. If you don't, the storage engine automatically adds a hidden primary key for you. In both cases, the primary keys are simply incrementing numeric values, similar to an AUTO-INCREMENT column. If you decide to add your own primary key at a later time, simply use ALTER TABLE to add one. Both storage engines wgill discard their internally generated keys in favor of yours.
Heap tables don't require a primary key but will create one for you. In fact, you can create Heap tables with no indexes at all.
AUTO_INCREMENT
Select Last_INSERT_ID(); 找到最新插入的AUTO_INCREMENT数。
对于多行插入，LAST_INSERT_ID()和mysql_insert_id()从插入的第一行实际返回AUTO_INCREMENT关键字。在复制设置中，通过该函数可以在其它服务器上正确复制多行插入。
要想以AUTO_INCREMENT值开始而不是1，你可以通过CREATE TABLE或ALTER TABLE来设置该值，如下所示:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
auto_increment_increment和auto_increment_offset用于主服务器－主服务器（master-to-master）复制，并可以用来控制AUTO_INCREMENT列的操作。
auto_increment_offset确定AUTO_INCREMENT列值的起点
auto_increment_increment控制列中的值的增量值
Show
Show Create TABLE ???
Show Create View ???
Show tables 显示当前数据库中有的表
Show databases 显示创建目前有的数据库
SHOW COLUMNS FROM mylibrary.titles 显示表的列
SHOW FIELDS FROM tbl_name
SHOW VARIABLES语句查看系统变量及其值
Drop table ???
Drop view ???
SHOW INDEX FROM tbl_name生成有关索引的信息。
SHOW STATUS
SHOW VARIABLES
SHOW ENGINE INNODB STATUS
SHOW TABLE STATUS FROM cookbook LIKE 'states'G
------------------------------------------------------------
Name: states
Type: InnoDB
Row_format: Dynamic
Rows: 50
Avg_row_length: 327
Data_length: 16384
Max_data_length: NULL
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Create_options:
Comment: InnoDB free: 479232 kB
---------------------------------------------------------------
创建数据库
mysqladmin -u root -p create mylibrary
mysql -u root -p mylibrary < mylibrary.sql
case-insensitive
MySQL is largely case-insensitive. The exception is in the names of databases and tables, where case distinction is made.
Limiting the number of resulting records
select .... from ...... LIMIT offset,n
offset specifies the number of the record at which access to the table is to begin. An offset of n skips the first n records and, since counting begins with 0, starts processing at record number n.)
SQL_CALC_FOUND_ROWS & FOUND_ROWS
Beginning with MySQL 4.0, you can use in a SELECT query the additional option SQL_CALC_FOUND_ROWS. Then, in a second query you can evaluate the SQL function FOUND_ROWS(), which tells how many records the query would have returned without LIMIT. The use of CALC_FOUND_ROWS and FOUND_ROWS is especially useful in complex queries in which a separate SELECT query for determining the number of records would be time intensive. Note, however, that the option CALC_FOUND_ROWS prevents certain kinds of optimization that MySQL carries out in LIMIT queries. Therefore, use CALC_FOUND_ROWS only when you then really wish to evaluate FOUND_ROWS.
------------------------------------------------------------------
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
--------------------------------------------------------------------
LIKE
Comparisons with LIKE can be very slow when they are applied to large tables. All the data records must be read and analyzed. It is impossible to use indexes to optimize such queries. A frequently used alternative to LIKE is the employment of a full-text index;
HAVING
Instead of formulating conditionals with WHERE, you could instead use HAVING. The WHERE conditionals are executed first, while HAVING conditionals are used only on intermediate results (returned by WHERE). The advantage of HAVING is that the conditions can also be applied to calculated fields (for example, to SUM(columnXy) in a GROUP BY query). An example appears in the section after next.
HAVING conditionals are less easily optimized for MySQL than WHERE conditionals, and they should be avoided if an equivalent WHERE is possible.
ISNULL
Please note that the conditional colname = NULL is not permitted. If you are searching for records that contain NULL, you must work with ISNULL(colname).
IS NULL
IS NOT NULL
IF(条件，输出1，输出二)
IFNULL(条件，输出)
IF(expr1 IS NOT NULL,expr1,expr2)
IFNULL(expr1,expr2)
LEFT JOIN
select d.*
from dept d left outer join emp e
on (d.deptno = e.deptno)
where e.deptno is null
ENUM
CREATE TABLE testenum (color ENUM ('red', 'green', 'blue', 'black', 'white'))
INSERT testenum VALUES ('red')
SELECT * FROM testenum WHERE color='red'
Creating Copies of a Table
The following instruction creates a new table with the name newtable and copies all the data records of table into the new table. The column definitions of the new table are identical to those of the old one, but there are some occasional differences. For example, the attribute AUTO_INCREMENT is lost. Moreover, in the new table, no indexes are created:
CREATE TABLE newtable SELECT * FROM table
Restoring Tables
With the given commands, first all data records of the original table table are deleted. Then the records that were saved into newtable are copied back into table. (The original AUTO_INCREMENT values remain untouched during the copying back and forth.)
DELETE FROM table
INSERT INTO table SELECT * FROM newtable
If you no longer require the backup data, you can simply delete newtable:DROP TABLE newtable
Making a Backup of an Entire Database
mysqldump -u loginame -p dbname > backupfile
Restoring a Database
mysql -u loginname -p dbname < backupfile
SOURCE backupfile;
Updating with Linked Tables
You can process the data from several tables using UPDATE commands (since MySQL 4.0). The following example changes the column columnA of table1, where the new data come from table2.columnB. The link between the two fields is established via the common ID field table1ID (a genuine example of a multitable UPDATE command is given in Chapter 10):
UPDATE table1, table1 SET table1.columnA = table.columnB WHERE table1.table1ID = table2.table1ID
Deleting Records from Linked Tables
DELETE t1, t2 FROM t1, t2, t3 WHERE condition1 AND condition2 ..
SET foreign_key_checks=0, and after the DELETE command reactivate it by SET foreign_key_checks=1.
Change the Character Set of All Text Columns
ALTER TABLE tblname CONVERT TO CHARACTER SET charsetname
Change the Type of the Table (MyISAM, InnoDB)
ALTER TABLE tblname ENGINE typename
If you wish to change the type of large number of tables, then under Unix/Linux, the use of the
script mysql_convert_table_format is to be recommended. If you do not specify any table names, all
tables of the database will be converted:
root# mysql_convert_table_format [opt] --type=InnoDB dbname [tblname]
FUNCTIONS
UUID() #UUID数
UUID 是一个由5位十六进制数的字符串表示的128比特数字 ，其格式为 aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee ：
- 前3个数字从一个时间戳产生。
- 第4 个数字保持暂时唯一性，以防时间戳值失去单一性 (例如, 由于经济时)。
- 第5个数字是一个 IEEE 802 节点号，它提供空间唯一性。若后者不可用，则用一个随机数字替换。 (例如, 由于主机没有以太网卡，或我们不知道怎样在你的操作系统上找到界面的机器地址 )。假若这样，空间唯一性就不能得到保证。尽管如此，一个冲突的发生机率还是非常低的。
BIT_COUNT
BIT_OR
concat(s1,s2,s3......)
substring(s,pos,n) pos=1
CHAR_LENGTH(s)
LEFT(s,length)
RIGHT(s,length
STRCMP
IF(a, b, c) a=tue 返回a 反之返回c
IFNULL(expr1, expr2)
ISNULL.
REPLACE(s, a, b)
LOCATE(s,a) //0 if it's not present
CONVERT( s using utf8 )
HEX
UTF8 比 lantin1多一个字符
INSERT INTO titles (title) VALUES( _utf8 'title in UTF-8 encoding)
MONTH
YEAR
DAYOFMONTH
DATE_FORMAT(ts,"%Y-%M")
SELECT DATE_FORMAT('2005-12-31', '%M %d %Y')
SELECT DATE_FORMAT('2005-12-31', '%D of %M')
SELECT TIME_FORMAT('02:17', '%H')
ADDDATE,DATE_ADD: These two equivalent functions add a time interval to a date
ADDDATE(‘2005-12-31 6:00’, INTERVAL ‘3:15:22’ HOUR_SECOND) returns 2005-31-12 9:15:22.
SUBDATE,DATE_SUB: As above, but now the time interval is subtracted.
SUBTIME (since MySQL 4.1): As above, but now a time is subtracted.
DATEDIFF (since MySQL 4.1): Returns the number of days between two dates. The time portions of the dates are ignored in the calculation.
DATEDIFF(‘2005-12-31’, ‘2005-12-28’) returns 3.
DATEDIFF(‘2005-12-31 00:00:00’, ‘2005-12-28 23:59:59’) also returns 3.
DATE_FORMAT()
TIMEDIFF (since MySQL 4.1): Returns the time difference between two times (TIME) or between two dates (DATETIME or TIMESTAMP). However, the function cannot be used to subtract
a time from a date.
TIMEDIFF(‘2005-12-31 12:30’, ‘2005-12-30 19:45’) returns 16:45:00.
RAND
NOW
CURTIME
CURDATE
HOST
LAST_INSERT_ID
CAST 将一个字符串转换到一个不同的字符集
LPAD(str,len,padstr) 返回字符串 str, 其左边由字符串padstr 填补到len 字符长度。假如str 的长度大于len, 则返回值被缩短至 len 字符。
TIME_TO_SEC( )
SEC_TO_TIME( )
TO_DAYS( )
FROM_DAYS( )
GROUP_CONCAT()
MOD() 对于带有小数部分的数值也起作用， 它返回除法运算后的精确余数：
SUBSTRING_INDEX(str,delim,count) 在定界符 delim 以及count 出现前，从字符串str返回自字符串。若count为正值,则返回最终定界符(从左边开始)左边的一切内容。若count为负值，则返回定界符（从右边开始）右边的一切内容。
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
INET_ATON(expr) 给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。地址可以是4或8比特地址。
mysql> SELECT INET_ATON('209.207.224.40');
-> 3520061480
COALESCE () The COALESCE function takes one or more values as arguments. The function returns the first non-null value in the list.
SESSION_USER() user@hostname
TIMESTAMP
For the automatic TIMESTAMP updating to function properly, the column involved must have either no explicit value assigned or else NULL
If there is more than one TIMESTAMP column in a table, then the first column is updated for which a constant time value is not explicitly defined (DEFAULT 0)
It can happen, however, that in correcting a record you wish to avoid changing the TIMESTAMP value. For this, you can deal with the TIMESTAMP column explicitly with SET, returning the column to its previous value. The following example assumes that the TIMESTAMP column has the name ts:
UPDATE table SET data='new text', ts=ts WHERE id=123
TIME ZONES
Server and Client Time Zones
Server Time Zone
SELECT @@global.time_zone
SYSTEM
SHOW VARIABLES LIKE 'system_time_zone'
CET
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP)
01:00:00
MySQL users who have SUPER privileges can change the time zone of the MySQL server on the fly with SET GLOBAL time_zone = …. However, this works only if no binary logging and no
replication are engaged. If that is the case, you must change default-time-zone in my.cnf/my.ini (see Chapter 22) and then restart the MySQL server.
ENUM
With ENUM you can manage a list of up to 65,535 character strings. Each string is given a number in sequence, and these are stored by MySQL in the ENUM column instead of the string itself. This saves on space and speeds up processing. Note that in each field of an ENUM column you can save only a single string, that is, a single number associated with that string. Saving multiple strings in such a field is impossible. (If you want to store a combination of several predefined strings, you can use a SET column.) In queries involving comparison of character strings there is no case distinction.
SET
SET uses a similar idea, though here arbitrary combinations are possible. Internally, the character strings are ordered by powers of 2 (1, 2, 4, 8, etc.), so that a bitwise combination is possible. The storage requirement is correspondingly larger (one bit per character string). At most 64 character strings can be combined (in which case the storage requirement is 8 bytes). For a combination of several character strings to be stored in one field, these must be given separated by commas (and with no blank characters between strings). The order of the strings is irrelevant and is not considered. In query results, combinations are always specified in the order in which the set was defined:
CREATE TABLE testset
(fontattr SET ('bold', 'italic', 'underlined'))
INSERT testset VALUES ('bold,italic')
In queries using the operator “=” an exact comparison is made of the entire combination.
In order to locate records in which an attribute has been set (regardless of its combination with other attributes), the MySQL function FIND_IN_SET can be used. This function returns the position of the sought character string within the set (in our example, 1 if ‘bold’ is found, 2 for ‘italic’, etc.):
SELECT * FROM testset WHERE FIND_IN_SET('italic', fontattr)>0
Variables
Ordinary variables: @
System and server variables: @@
Many system variables exist in two forms, one specifically for the current connection (e.g., @@session.wait_timeout) and one globally for the MySQL server (e.g., @@global.wait_timeout containing the default value for this variable).
Variable Assignment
SET @varname = 3
SELECT @varname := 3
CASE Branching
CASE expr
WHEN val1 THEN result1
WHEN val2 THEN result2
...
ELSE resultn
END
CASE
WHEN cond1 THEN result1
WHEN cond2 THEN result2
...
ELSE resultn
END
SELECT title FROM titles
WHERE langID=1
ORDER BY
CASE
WHEN LEFT(title,2)="A " THEN MID(title,3)
WHEN LEFT(title,3)="An " THEN MID(title,4)
WHEN LEFT(title,4)="The " THEN MID(title,5)
ELSE title
END
Creating a New Table by Copying
If you now look at the table definition with SHOW CREATE TABLE oldtable or newtable, you will discover that in the process of copying, the indexes and AUTO_INCREMENT attribute for the column publID, as well as some TIMESTAMP attributes, have gone missing. Furthermore, the table type has changed from InnoDB to MyISAM.
To avoid such unwanted changes in the table structure, it is preferable first to create the new table just like the old one with a separate CREATE TABLE command and then use INSERT INTO …SELECT …
CREATE TABLE publishers LIKE mylibrary.publishers
INSERT INTO publishers SELECT * FROM mylibrary.publishers
When you use INSERT ... SELECT, you cannot use the same table both as a source and a destination.
SOUNDEX
RAND()
SELECT titleID, title FROM titlescopy ORDER BY RAND() LIMIT 1
Setting Auto Commit Mode
SET AUTOCOMMIT = 0.
Transactions and Locking
START TRANSACTION;
COMMIT;
使用START TRANSACTION，autocommit仍然被禁用，直到您使用COMMIT或ROLLBACK结束事务为止。然后autocommit模式恢复到原来的状态
SELECT … LOCK IN SHARE MODE
SELECT … FOR UPDATE
Communication on a Local Computer
TCP/IP
Socket file (Unix/Linux only):
Named pipes (only Windows 2000/XP):
Shared memory (only Windows 2000/XP):
Determining the Communication Forms Supported by the MySQL Server
SHOW VARIABLES LIKE 'skip_net%'
SHOW VARIABLES LIKE 'port'
SHOW VARIABLES LIKE 'socket'
SHOW VARIABLES LIKE 'named%'
SHOW VARIABLES LIKE 'shared%'
Replace root with another name:
UPDATE user SET user = 'myroot' WHERE user = 'root'
Granting the Right to Create One’s Own Database
SHOW PRIVILEGES //显示权限项
SHOW PROCESSLIST
The privilege Super permits the user to end both his own and others’ processes with KILL. (If the Super privilege has not been granted, then only the current process can be ended.)
The Super privilege also permits the execution of some administrative commands: CHANGE MASTER for executing the client configuration of a replication system, PURGE MASTER to delete binary logging files, and SET GLOBAL for changing global MySQL variables.
MySQL Types to Java Types for ResultSet.getObject().
A SELECT command will give you a quick overview of all users.
SELECT user, host, password FROM user
Set the root password
UPDATE user SET password = PASSWORD('secret') WHERE user =......
Replace root with another name
UPDATE user SET user = 'myroot' WHERE user = 'root'
Deal with users who can log in from anywhere
The following command allows logins only from the local network (which in this example has the name sol):
UPDATE user SET host = '%.sol' WHERE host = '%'
FLUSH PRIVILEGES
For all these commands to become effective, you must now execute the command FLUSH PRIVILEGES. MySQL keeps a copy of the mysql database in RAM, for speed optimization, which
is updated via FLUSH PRIVILEGES:
FLUSH PRIVILEGES
GRANT
GRANT All ON forum.* TO forumadmin@localhost IDENTIFIED BY 'xxx'
GRANT Select, Insert, Update, Delete ON forum.* TO forumuser@localhost IDENTIFIED BY 'xxx'
mysqldump
导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump --opt database > backup-file.sql
导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
导出一个数据库结构
mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:wcnc_db.sql
-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table
但是它对用来自于一个数据库的信息充实另外一个MySQL数据库也是有用的：
mysqldump --opt database | mysql --host=remote-host -C database
用mysqldump备份和恢复指定表
mysqldump -u user -p db tab1 tab2 > db.sql
Mysql Tables for Administration of Access Privileges
In MySQL’s two-tiered access system, the table user is solely responsible for the first level (that is, for the connection to MySQL). The user table contains all global privileges. For the second tier (that is, access to specific objects: databases, tables, and columns) it is the tables db, host, tables_priv, and columns_priv that are responsible, in addition to user. The tables db, host, tables_priv, and columns_priv come into play in this order when the privileges for their respective tiers are set to N (which stands for “No”). In other words, if the Select privilege is granted to a user in user, then the other four tables will not be consulted in checking the permissibility of a SELECT command executed by that user.
The db table contains information on which databases a particular user is permitted to read, edit, and delete.
Mysql Client
prompt 修改客户端提示
prompt mysql (d)
prompt mysql (u@h)>
MySQL修改密码方法总结
首先要说明一点的是：一般情况下，修改MySQL密码是需要有mysql里的root权限的，这样一般用户是无法更改密码的，除非请求管理员帮助修改。
方法一
使用phpMyAdmin (图形化管理MySql数据库的工具)，这是最简单的，直接用SQL语句修改mysql数据库库的user表，不过别忘了使用PASSWORD函数，插入用户用Insert命令，修改用户用Update命令，删除用Delete命令。在本节后面有数据表user字段的详细介绍。
方法二
使用mysqladmin。输入
mysqladmin -u root -p oldpassword newpasswd
执行这个命令后，需要输入root的原密码，这样root的密码将改为newpasswd。同样，把命令里的root改为你的用户名，你就可以改你自己的密码了。 当然如果你的mysqladmin连接不上mysql server，或者你没有办法执行mysqladmin，那么这种方法就是无效的，而且mysqladmin无法把密码清空。
下面的方法都在mysql提示符下使用，且必须有mysql的root权限：
方法三
mysql> INSERT INTO mysql.user (Host,User,Password) VALUES ('%','system', PASSWORD('manager'));
mysql> FLUSH PRIVILEGES
确切地说这是在增加一个用户，用户名为system，密码为manager。注意要使用PASSWORD函数，然后还要使用FLUSH PRIVILEGES来执行确认。
方法四
和方法三一样，只是使用了REPLACE语句
mysql> REPLACE INTO mysql.user (Host,User,Password)
VALUES('%','system',PASSWORD('manager'));
mysql> FLUSH PRIVILEGES
方法五
使用SET PASSWORD语句
mysql> SET PASSWORD FOR system@"%" = PASSWORD('manager');
你也必须使用PASSWORD()函数，但是不需要使用FLUSH PRIVILEGES来执行确认。
方法六
使用GRANT ... IDENTIFIED BY语句，来进行授权。
mysql> GRANT USAGE ON *.* TO system@"%" IDENTIFIED BY 'manager';
这里PASSWORD()函数是不必要的，也不需要使用FLUSH PRIVILEGES来执行确认。
注：PASSWORD()函数作用是为口令字加密，在程序中MySql自动解释。
二、MySql中访问限制的设置方法
我们采用两种方法来设置用户。
进入到Mysql执行目录下（通常是c:mysqlbin）。输入mysqld-shareware.exe，输入mysql --user=root mysql ,不然不能添加新用户。进入到mysql>提示符下进行操作。
假设我们要建立一个超级用户，用户名为system，用户口令为manager。
方法一
用Grant 命令授权，输入的代码如下：
mysql>GRANT ALL PRIVILEGES ON *.* TO system@localhost IDENTIFIED BY 'manager' WITH GRANT OPTION;
应显示:Query OK, 0 rows affected (0.38 sec)
方法二
对用户的每一项权限进行设置：
mysql>INSERT INTO user VALUES('localhost','system',PASSWORD('manager'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
对于3.22.34版本的MySQL,这里共14个"Y",其相应的权限如下(按字段顺序排列):
| 权限 | 表列名称 | 相应解释 | 使用范围 |
| select | Select_priv | 只有在真正从一个表中检索时才需要select权限 | 表 |
| insert | Insert_priv | 允许您把新行插入到一个存在的表中 | 表 |
| update | Update_priv | 允许你用新值更新现存表中行的列 | 表 |
| delete | Delete_priv | 允许你删除满足条件的行 | 表 |
| create | Create_priv | 允许你创建新的数据库和表 | 数据库、表或索引 |
| drop | Drop_priv | 抛弃(删除)现存的数据库和表 | 数据库或表 |
| reload | Reload_priv | 允许您告诉服务器再读入授权表 | 服务器管理 |
| shutdown | Shutdown_priv | 可能被滥用(通过终止服务器拒绝为其他用户服务) | 服务器管理 |
| process | Process_priv | 允许您察看当前执行的查询的普通文本,包括设定或改变口令查询 | 服务器管理 |
| file | File_priv | 权限可以被滥用在服务器上读取任何可读的文件到数据库表 | 服务器上的文件存取 |
| grant | Grant_priv | 允许你把你自己拥有的那些权限授给其他的用户 | 数据库或表 |
| references | References_priv | 允许你打开和关闭记录文件 | 数据库或表 |
| index | Index_priv | 允许你创建或抛弃(删除)索引 | 表 |
| alter | Alter_priv | 允许您改变表格,可以用于通过重新命名表来推翻权限系统 | 表 |
如果创建用户时只有select、insert、update和delete权限,则允许用户只能在一个数据库现有的表上实施操作.
下面就可以创建我们要用到的数据库了,我们直接输入. 例如：我们要创建数据库名为XinXiKu，可用如下代码：
mysql>create database XinXiKu;
应显示:Query OK, 1 row affected (0.00 sec)
Differences Between Procedures and Functions
There are two types of stored procedures: procedures and functions. For now, suffice it to say that functions can return values,
while procedures support reference parameters and a greater variety of SQL commands (e.g., SELECT and INSERT). Procedures must be called with CALL, while functions can be embedded in ordinary SQL commands.
change delimiter
delimiter $$
delimiter ;
Determining Existing SPs
The two commands SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS return a list of all defined procedures and functions. More complete information is provided by the table information_schema.routines. It contains all data of the table mysql.proc
Determining the Code of an SP
SHOW CREATE FUNCTION/PROCEDURE name
Setting the root Password
mysqladmin -u root -h computername password xxx
MySQL Server Configuration File
Windows C:ProgramsMySQL Server n.nmy.ini
Unit/Linux /etc/my.cnf
The server-specific options in the configuration file begin with the line [mysqld].
grant
You need to grant privileges for the database even if the user account already exists. However, in that case, you'll likely want to omit the IDENTIFIED BY 'cbpass' part of the statement, because otherwise you'll change that account's current password. The hostname part of 'cbuser'@'localhost' indicates the host from which you'll be connecting to the MySQL server to access the cookbook database.
Specifying Connection Parameters by Using Option Files
Under Unix, your personal option file is named .my.cnf in your home directory. There are also site-wide option files that administrators can use to specify
parameters that apply globally to all users. You can use /etc/my.cnf or the my.cnf file in the MySQL server's data directory. Under Windows, the option files you can use are C:my.cnf, the my.ini file in your Windows system directory, or my.cnf in the server's data directory.
--------------------------------
[client]
user=root
password=password
------------------------------------
Current DATABASE
If you've forgotten or are not sure which database is the current one
SELECT DATABASE( );
DATABASE( ) is a function that returns the name of the current database.
Canceling a Partially Entered Query
Cancel the query using your line kill character or the c sequence.
Use a SQL variable to store the value for later use.
mysql> SELECT @id := cust_id FROM customers WHERE cust_id=' customer name;
mysql> DELETE FROM customers WHERE cust_id = @id;
mysql> DELETE FROM orders WHERE cust_id = @id;
mysql> SELECT @max_limbs := MAX(arms+legs) FROM limbs;
mysql> SELECT @last_id := LAST_INSERT_ID( );
SQL variables hold single values. If you assign a value to a variable returns multiple rows, the value from the last row is used
If the statement returns no rows, no assignment takes place and the variable retains its previous value. If the variable has not been used previously, that value is NULL:
Variable names are case sensitive:
Telling mysql to Read Queries from Other Programs--PIPE
cat limbs.sql | mysql cookbook
mysqldump cookbook | mysql -h some.other.host.com cookbook
Specifying Queries on the Command Line
mysql -e "语句"
mysql -e "SELECT COUNT(*) FROM limbs" cookbook
Producing HTML Output
mysql -H -e "SELECT * FROM limbs WHERE legs=0" cookbook
Specifying Queries on the Command Line
mysql -e "语句"
mysql -e "SELECT COUNT(*) FROM limbs" cookbook
Logging Interactive mysql Sessions
Create a tee file.
mysql --tee=tmp.out cookbook
To control session logging from within mysql, use T and t to turn tee output on and off. This is useful if you want to record only parts of a session:
mysql> T tmp.out --Logging to file 'tmp.out'
mysql> t --Outfile disabled.
MysqlDump
C:Documents and Settingsmypc>mysqldump -u root -p --default-character-set=utf8 -r c:033001.sql
--add-drop-database=false --skip-opt --create-options=true test
Create table from select sentence
CREATE TABLE dst_tbl SELECT * FROM src_tbl;
If you want to create an empty table, use a WHERE clause that is always false:
CREATE TABLE dst_tbl SELECT * FROM src_tbl WHERE 0;
CREATE TABLE dst_tbl
(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
)
SELECT a, b, c FROM src_tbl;
CREATE TABLE dst_tbl (PRIMARY KEY (id), INDEX(state,city))
SELECT * FROM src_tbl;
TEMPORARY TABLE
CREATE TEMPORARY TABLE. This statement is just like CREATE TABLE except that it creates a transient table that disappears when your connection to the server closes, if you haven't already removed it yourself.
Another property of temporary tables is that they can be created with the same name as a permanent table. In this case, the temporary table "hides" the permanent table for the
duration of its existence, which can be useful for making a copy of a table that you can modify without affecting the original by mistake.
mysql> CREATE TEMPORARY TABLE mail SELECT * FROM mail;
mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
| 16 |
+----------+
mysql> DELETE FROM mail;
mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
mysql> DROP TABLE mail;
mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
| 16 |
+----------+
Alter column defination
To remove a column from a table, use DROP followed by the column name.
To add a column, use ADD and specify the column definition.
To indicate that you want a column at a specific position within the table, either use FIRST to make it the first column, or AFTER col_name to indicate that the new column should be
placed after col_name. The FIRST and AFTER specifiers work only with the ADD clause.
Use SET DEFAULT to specify the default value explicitly, or DROP DEFAULT to remove the current default and allow MySQL to assign the "default default."
To change a default value, use ALTER col_name SET DEFAULT
To drop a default value, use ALTER col_name DROP DEFAULT
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);
ALTER TABLE tbl_name ADD INDEX index_name (column_list);
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);
ALTER TABLE tbl_name DROP PRIMARY KEY;
ALTER TABLE tbl_name DROP INDEX index_name;
Change a Table
Changing a Table Type
ALTER TABLE tbl_name TYPE = MYISAM;
Renaming a Table
ALTER TABLE old_name RENAME TO new_name;
1）增加列
mysql>alter table 表名 add 列名 列的数据类型
例如,给表pet增加一列weight
mysql>alter table pet add weight varchar(20);
(2)删除列
alter table 表名 drop 列名
例如，删除列weight：
mysql>alter table pet drop weight;
(3)改变列
alter table 表名 modify 列名 列的数据类型
例如，改变weight的类型：
mysql> alter table pet modify weight samllint;
另一种方法是：
alter table 表名 change 原来的列名 新的列名 列的数据类型
例如：
mysql> alter table pet change weight weight samllint;
(4)给列更名
mysql>alter table pet change weight wei char(20);
(5)给表更名
alter table 表名 rename 新的表名
例如，把pet表更名为animal
mysql>alter table pet rename animal;
(6)增加索引
alter table 表名 add index(wei)
regular strings or binary stringsBinary data may contain bytes that lie outside the usual range of printable ASCII characters.
A binary string in MySQL is one that MySQL treats as case sensitive in comparisons. For binary strings, the characters A and a are considered different. For non-binary strings, they're considered the same.
Writing Strings That Include Quotes or Special Characters
MySQL, unlike some SQL engines, allows you to quote strings with either single quotes or double quotes, so you can enclose a string containing single quotes within double quotes.
a string containing double quotes can be enclosed within single quotes:
To include a quote character within a string that is quoted by the same kind of quote, either double the quote or precede it with a backslash.
Other escape sequences recognized by MySQL are b (backspace), n (newline, also called linefeed), r (carriage return), t (tab), and 0 (ASCII NUL).
Pattern match
Unlike some other databases, MySQL allows pattern matches to be applied to numeric or date values, which can sometimes be useful.
To make a string comparison case sensitive that normally would not be, cast (convert) one of the strings to binary form by using the BINARY keyword. It doesn't matter which of the strings you make binary.
To make a string comparison not case sensitive that normally would be, convert both strings to the same lettercase using UPPER( ) or LOWER( )
ENUM and SET columns are not case sensitive. Furthermore, because they are stored internally as numbers, you cannot declare them case sensitive in the table definition by adding the BINARY keyword.
DATE & TIME
DATE CCYY-MM-DD
TIME hh:mm:ss
DATETIME CCYY-MM-DD hh:mm:ss
TIMESTAMP CCYYMMDDhhmmss
MySQL always stores dates in ISO format
DATE_FORMAT( )
TIME_FORMAT( )
ORDER BY
ORDER BY has the following general characteristics:
You can sort using a single column of values or multiple columns
You can sort any column in either ascending order (the default) or descending order
You can refer to sort columns by name, by their position within the output column list,or by using an alias
Multiple-column sorts can be descending as well, but DESC must be specified after each column name to perform a fully descending sort:
mysql> SELECT * FROM driver_log ORDER BY name DESC, trav_date DESC;
The ORDER BY clauses in the queries shown thus far refer to the sorted columns by name. You can also name the columns by their positions within the output column list or by using aliases. Positions within the output list begin with 1.
mysql> SELECT name, trav_date, miles FROM driver_log ORDER BY 3;
The ORDER BY columns need not even be the ones you display, ORDER BY is not limited to sorting only those columns named in the column output list.
When a sorted column contains NULL values, MySQL puts them all together in the sort order. From 4.0.2 on, MySQL sorts NULL values according to the ANSI SQL specification, and thus always places them first in the sort order, regardless of whether or not you specify DESC.
String sorts are case sensitive
文件系统文件大小
| 操作系统 | 文件大小限制 |
| Linux 2.2-Intel 32-bit | 2GB (LFS: 4GB) |
| Linux 2.4+ | (using ext3 filesystem) 4TB |
| Solaris 9/10 | 16TB |
| NetWare w/NSS filesystem | 8TB |
| win32 w/ FAT/FAT32 | 2GB/4GB |
| win32 w/ NTFS | 2TB（可能更大） |
| MacOS X w/ HFS+ | 2TB |
MySQL数据库的最大有效表尺寸通常是由操作系统对文件大小的限制决定的，而不是由MySQL内部限制决定的。
BENCHMARK
执行定时测试 mysql> SELECT BENCHMARK(1000000,1+1)；
+------------------------+
|BENCHMARK(1000000,1+1) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.32 sec) 它显示MySQL在该系统上在0.32秒内可以执行1,000,000个简单的+表达式运算
MIN( ) and MAX( )
aggregate functions like MIN( ) and MAX( ) cannot be used in WHERE clauses.
The query fails because MySQL uses the WHERE clause to determine which records to select, but it knows the value of an aggregate function only after selecting the records from which the function's value is determined! So, in a sense, the statement is self-contradictory.
MIN( ) and MAX( ) select strings in case sensitive fashion
MYSQL 提示符
各个提示符并简述它们所表示的mysql的状态：
| 提示符 | 含义 |
| mysql> | 准备好接受新命令。 |
| -> | 等待多行命令的下一行。 |
| '> | 等待下一行，等待以单引号(“'”)开始的字符串的结束。 |
| "> | 等待下一行，等待以双引号(“"”)开始的字符串的结束。 |
| `> | 等待下一行，等待以反斜点(‘`’)开始的识别符的结束。 |
| /*> | 等待下一行，等待以/*开始的注释的结束。 |
|
|
| ALLOW_INVALID_DATES | 在严格模式下不要检查全部日期 |
| ANSI_QUOTES | 将‘"’视为识别符引号(‘`’引号字符)，不要视为字符串的引号字符。在ANSI模式，你可以仍然使用‘`’来引用识别符。启用ANSI_QUOTES后，你不能用双引号来引用字符串，因为它被解释为识别符。 |
| ERROR_FOR_DIVISION_BY_ZERO | 在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作结果为NULL。 |
| HIGH_NOT_PRECEDENCE | NOT操作符的优先顺序是表达式例如NOT a BETWEEN b AND c被解释为NOT (a BETWEEN b AND c)。在一些旧版本MySQL中， 表达式被解释为(NOT a) BETWEEN b AND c。启用HIGH_NOT_PRECEDENCESQL模式，可以获得以前的更高优先级的结果。 |
| IGNORE_SPACE | 允许函数名和‘(’之间有空格。强制将所有函数名视为保存的字。结果是，如果你想要访问保存为字的数据库、表或列名，你必须引用它。例如，因为有USER()函数，mysql数据库中的user表名和该表内的User列被保存下来，因此你必须引用它们 |
| NO_AUTO_CREATE_USER | 防止GRANT自动创建新用户，除非还指定了密码。 |
| NO_AUTO_VALUE_ON_ZERO | NO_AUTO_VALUE_ON_ZERO影响AUTO_INCREMENT列的处理。一般情况，你可以向该列插入NULL或0生成下一个序列号。NO_AUTO_VALUE_ON_ZERO禁用0，因此只有NULL可以生成下一个序列号。 如果将0保存到表的AUTO_INCREMENT列，该模式会很有用。(不推荐采用该惯例)。例如，如果你用mysqldump转储表并重载，MySQL遇到0值一般会生成新的序列号，生成的表的内容与转储的表不同。重载转储文件前启用NO_AUTO_VALUE_ON_ZERO可以解决该问题。mysqldump在输出中自动包括启用NO_AUTO_VALUE_ON_ZERO的语句。 |
| NO_BACKSLASH_ESCAPES | 禁用反斜线字符(‘’)做为字符串内的退出字符。启用该模式，反斜线则成为普通字符。 |
| NO_DIR_IN_CREATE | 创建表时，忽视所有INDEX DIRECTORY和DATA DIRECTORY指令。该选项对从复制服务器有用。 |
| NO_ENGINE_SUBSTITUTION | 如果需要的存储引擎被禁用或未编译，可以防止自动替换存储引擎 |
| NO_FIELD_OPTIONS | 不要在SHOW CREATE TABLE的输出中打印MySQL专用列选项。该模式在可移植模式（portability mode）下用于mysqldump |
| NO_KEY_OPTIONS | 不要在SHOW CREATE TABLE的输出中打印MySQL专用索引选项。该模式在可移植模式（portability mode）下用于mysqldump |
| NO_TABLE_OPTIONSc | 不要在SHOW CREATE TABLE的输出中打印MySQL专用表选项（例如ENGINE）。该模式在可移植模式（portability mode）下用于mysqldump |
| NO_UNSIGNED_SUBTRACTION | 在减运算中，如果某个操作数没有符号，不要将结果标记为UNSIGNED。请注意这样使UNSIGNED BIGINT不能100%用于上下文中。 |
| NO_ZERO_DATE | 在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告。 |
| NO_ZERO_IN_DATE | 严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。 |
| ONLY_FULL_GROUP_BY | 不要让GROUP BY部分中的查询指向未选择的列 |
| PIPES_AS_CONCAT | 将||视为字符串连接操作符（＋）(同CONCAT())，而不视为OR。 |
| REAL_AS_FLOAT | 将REAL视为FLOAT的同义词，而不是DOUBLE的同义词。 |
存储引擎
要想找出你的服务器支持哪个存储引擎，执行下面的语句：
mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+----------------------------------------------------------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
............................
+------------+---------+----------------------------------------------------------------+
18 rows in set (0.00 sec)
你还可以使用下面的语句代替SHOW ENGINES，并检查你感兴趣的存储引擎的变量值：
mysql> SHOW VARIABLES LIKE 'have%';
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | YES |
| have_compress | YES |
| have_crypt | YES |
+-----------------------+----------+
YES 支持该特性并已经激活; NO 不支持该特性;DISABLED 支持该特性但被禁用。
所有MySQL服务器支持MyISAM表，因为MyISAM是 默认存储引擎。
服务器模式
这样每个应用程序可以根据自己的需求来定制服务器的操作模式。模式定义MySQL应支持哪些SQL语法，以及应执行哪种数据验证检查。这样可以更容易地在不同的环境中使用MySQL，并结合其它数据库服务器使用MySQL。你可以用--sql-mode="modes"选项启动mysqld来设置默认SQL模式
你还可以在启动后用SET [SESSION|GLOBAL] sql_mode='modes'语句设置sql_mode变量来更改SQL模式。设置 GLOBAL变量时需要拥有SUPER权限，并且会影响从那时起连接的所有客户端的操作。设置SESSION变量只影响当前的客户端。任何客户端可以随时更改自己的会话 sql_mode值。
modesis是用逗号(‘，’)间隔开的一系列不同的模式。你可以用SELECT @@sql_mode语句查询当前的模式。
系统变量
| 变量名 | 含义 |
| basedir | MySQL安装基准目录。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
日志文件
MySQL有几个不同的日志文件，可以帮助你找出mysqld内部发生的事情：
日志文件 记入文件中的信息类型
错误日志 记录启动、运行或停止mysqld时出现的问题。
查询日志 记录建立的客户端连接和执行的语句。
更新日志 记录更改数据的语句。不赞成使用该日志。
二进制日志 记录所有更改数据的语句。还用于复制。
慢日志 记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。
错误日志
可以用--log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值，mysqld使用错误日志名host_name.err 并在数据目录中写入日志文件。如果你执行FLUSH LOGS，错误日志用-old重新命名后缀并且mysqld创建一个新的空日志文件。(如果未给出--log-error选项，则不会重新命名）。
如果不指定--log-error，或者(在Windows中)如果你使用--console选项，错误被写入标准错误输出stderr。通常标准输出为你的终端。在Windows中，如果未给出--console选项，错误输出总是写入.err文件。
查询日志
所有连接和语句被记录到日志文件。当你怀疑在客户端发生了错误并想确切地知道该客户端发送给mysqld的语句
二进制日志
二进制日志以一种更有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。时，该日志可能非常有用。 二进制日志已经代替了老的更新日志，更新日志在MySQL 5.1中不再使用。 二进制日志还包含关于每个更新数据库的语句的执行时间信息。二进制日志的主要目的是在恢复使能够最大可能地更新数据库。因为二进制日志包含备份后进行的所有更新。二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。运行服务器时若启用二进制日志则性能大约慢1%。但是，二进制日志的好处，即用于恢复并允许设置复制超过了这个小小的性能损失。
可以用mysqlbinlog实用工具检查二进制日志文件。如果你想要重新处理日志止的语句，这很有用。例如，可以从二进制日志更新MySQL服务器，方法如下：
shell> mysqlbinlog log-file | mysql -h server_name
慢日志
慢查询日志可以用来找到执行时间长的查询，可以用于优化。但是，检查又长又慢的查询日志会很困难。要想容易些，你可以使用mysqldumpslow命令获得日志中显示的查询摘要来处理慢查询日志。
MYSQL 安装
在同一台机器上运行多个MySQL服务器，至少下面的选项对每个服务器必须是不同的：
--port=port_num --port控制着TCP/IP连接的端口号。
-socket=path --socket控制Unix中的Unix套接字文件路径和在Windows中的命名管道名称。在Windows中，只有支持命名管道连接的服务器才需要明确指定管道名称。
--shared-memory-base-name=name 该选项当前只在Windows中使用。它指定Windows服务器使用的、允许客户端通过共享内存来连接的共享内存名。
--pid-file=path 该选项只在Unix中使用。它指出服务器在其中写入进程ID的文件名。
如果你使用下面的日志文件选项，对于每个服务器来说，它们必须是不同的：
--log=path
--log-bin=path
--log-update=path
--log-error=path
--bdb-logdir=path
为了提高性能，你可以为每个服务器指定下面选项的不同的值，以便在物理磁盘之间平均分配负荷：
--tmpdir=path
--bdb-tmpdir=path
如果在不同的位置有多个MySQL的安装，一般情况可以用--basedir=path选项为每个服务器指定基本安装目录，使每个服务器使用不同的数据目录、日志文件和PID文件。（所有这些值的 默认值相对于根目录来确定）。 那样的话， 你唯一需要指定的其它选项是--socket和--port选项。
LOAD DATA
By default, LOAD DATA expects the datafile to contain the same number of columns as the table into which you're loading data, and the datafile columns must be present in the same order as in the table. If the file doesn't contain a value for every column or the values aren't in the proper order, you can specify which columns are present and the order in which they appear. If the datafile contains fewer columns than the table,
MySQL assigns default values to columns for which no values are present in the datafile.
b backspace
N NULL
n linefeed 0x0A
r carriage return 0x0D
t tab 0x09
\ backslash
0 ASCII NULL 0x00
By default, LOAD DATA assumes that datafiles contain lines that are terminated by linefeeds (newlines) and that data values within a line are separated by tabs. use a FIELDS clause to describe the characteristics of fields within a line, and a LINES clause to specify the line-ending sequence.
-----------------------------------------------------
LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl
-> FIELDS TERMINATED BY ':'
-> LINES TERMINATED BY 'r';
-----------------------------------------------------
If both are present, the FIELDS clause must precede the LINES clause.
MySQL的大小写敏感性
在 MySQL 中，数据库和表对就于那些目录下的目录和文件。因而，操作系统的敏感性决定数据库和表命名的大小写敏感。这就意味着数据库和表名在 Windows 中是大小写不敏感的，而在大多数类型的 Unix 系统中是大小写敏感的。奇怪的是列名与列的别名在所有的情况下均是忽略大小写的，而表的别名又是区分大小写的。要避免这个问题，你最好在定义数据库命名规则的时候就全部采用小写字母加下划线的组合，而不使用任何的大写字母。或者也可以强制以
-O lower_case_table_names=1 参数启动
mysqld（如果使用
--defaults-file=...my.cnf 参数来读取指定的配置文件启动
mysqld 的话，你需要在配置文件的
[mysqld] 区段下增加一行
lower_case_table_names=1）。这样MySQL 将在创建与查找时将所有的表名自动转换为小写字符（这个选项缺省地在 Windows 中为 1 ，在 Unix 中为 0。从 MySQL 4.0.2 开始，这个选项同样适用于数据库名）。 当你更改这个选项时，你必须在启动
mysqld 前首先将老的表名转换为小写字母。换句话说，如果你希望在数据库里面创建表的时候保留大小写字符状态，则应该把这个参数置0：
lower_case_table_names=1 。否则的话你会发现同样的sqldump脚本在不同的操作系统下最终导入的结果不一样（在Windows下所有的大写字符都变成小写了）。
foreign key & index
You can use the SHOW INDEX command to retrieve index information such as index name, columns in the index, and ordinal position of the columns in the index. Additionally, you can query INFORMATION_SCHEMA.KEY_COLUMN_USAGE to list the foreign keys for a given table. In MySQL 5, foreign keys are said to be indexed automatically, but can in fact be dropped. To determine whether a foreign key column's index has been dropped you can execute SHOW INDEX for a particular table and compare the output with that of INFORMATION_SCHEMA.KEY_ COLUMN_USAGE.COLUMN_NAME for the same table. If the COLUMN_NAME is listed in KEY_COLUMN_USAGE but is not returned by SHOW INDEX, you know that column is not indexed.
MySQL Replication
MySQL natively supports one-way, asynchronous replication. MySQL Replication works by simply having one server act as a master, while one or more servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster.
Having multiple masters configured within your replication topology is possible, but does raise issues not present in a single master system. Special attention should be paid to understand the limitations and possible problems of such a configuration.
MySQL Cluster
MySQL Cluster implements a distributed,highly fault tolerant architecture with no single point of failure. MySQL Cluster can be described as a shared-nothing, synchronous database cluster which supports automatic fail over, transactions and in-memory data storage without any special networking, hardware or storage requirements.eliminated. Any node can fail without affecting the system as a whole. An application, for example, can continue executing transactions even though a data node has failed. MySQL Cluster has also proven to handle tens of thousands of distributed transactions per second, replicated across data nodes.
SQL_SMALL_RESULT 有何用途 之前刚接触 MySQL 的时候，同事告诉我如果查询的资料结果很小，可以加上 SQL_SMALL_RESULT 来优化，可是最近查找 MySQL 官方手册的时候发现，SQL_SMALL_RESULT 必须与 GROUP BY、DISTINCT 或 DISTINCTROW 一起使用。那是不是说没有使用上述那个修饰字的查询，加上 SQL_SMALL_RESULT 并没有效果呢？似乎并不是这样，在 MySQL 讲述 where 优化的的地方也有提到，假如使用 SQL_SMALL_RESULT，将会要求 MySQL使用内存临时数据表( in-memory temporary table)，所以，应该还是有效果的。至于何谓查询结果小的定义，可能要看需求面而定。
TRUNCATE VS DELETE
TRUNCATE [TABLE] tbl_name
TRUNCATE TABLE用于完全清空一个表。从逻辑上说，该语句与用于删除所有行的DELETE语句等同，但是在有些情况下，两者在使用上有所不同。对于InnoDB表，如果有需要引用表的外键限制，则TRUNCATE TABLE被映射到DELETE上；否则使用快速删减（取消和重新创建表）。使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器，设置时不考虑是否有外键限制。对于其它存储引擎，在MySQL 5.1中，TRUNCATE TABLE与DELETE FROM有以下几处不同：
· 删减操作会取消并重新创建表，这比一行一行的删除行要快很多。
· 删减操作不能保证对事务是安全的；在进行事务处理和表锁定的过程中尝试进行删减，会发生错误。
· 被删除的行的数目没有被返回。
· 只要表定义文件tbl_name.frm是合法的，则可以使用TRUNCATE TABLE把表重新创建为一个空表，即使数据或索引文件已经被破坏。
· 表管理程序不记得最后被使用的AUTO_INCREMENT值，但是会从头开始计数。即使对于MyISAM和InnoDB也是如此。MyISAM和InnoDB通常不再次使用序列值。
· 当被用于带分区的表时，TRUNCATE TABLE会保留分区；即，数据和索引文件被取消并重新创建，同时分区定义（.par）文件不受影响。
TRUNCATE TABLE是在MySQL中采用的一个Oracle SQL扩展。
Storage engine features in MySQL
TRUNCATE [TABLE] tbl_nameTRUNCATE TABLE用于完全清空一个表。从逻辑上说，该语句与用于删除所有行的DELETE语句等同，但是在有些情况下，两者在使用上有所不同。
对于InnoDB表，如果有需要引用表的外键限制，则TRUNCATE TABLE被映射到DELETE上；否则使用快速删减（取消和重新创建表）。使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器，设置时不考虑是否有外键限制。
对于其它存储引擎，在MySQL 5.1中，TRUNCATE TABLE与DELETE FROM有以下几处不同：
· 删减操作会取消并重新创建表，这比一行一行的删除行要快很多。
· 删减操作不能保证对事务是安全的；在进行事务处理和表锁定的过程中尝试进行删减，会发生错误。
· 被删除的行的数目没有被返回。
· 只要表定义文件tbl_name.frm是合法的，则可以使用TRUNCATE TABLE把表重新创建为一个空表，即使数据或索引文件已经被破坏。
· 表管理程序不记得最后被使用的AUTO_INCREMENT值，但是会从头开始计数。即使对于MyISAM和InnoDB也是如此。MyISAM和InnoDB通常不再次使用序列值。
· 当被用于带分区的表时，TRUNCATE TABLE会保留分区；即，数据和索引文件被取消并重新创建，同时分区定义（.par）文件不受影响。
TRUNCATE TABLE是在MySQL中采用的一个Oracle SQL扩展。
