MySQL技术手册要点解析-1
1.mysql为了可以兼容各种产品级别的主流数据库，设置了这个sql_mode 参数，比如为了兼容oracle,可以设置为sql_mode=oracle，为了兼容db2可以设置为sql_mode=db2等等...，这个参数可以在msqld启动时候加上，也可以在运行时，修改全局或者session的参数
1.8.2. Selecting SQL Modes
The MySQL server can operate in different SQL modes, and can apply these modes differentially for different clients. This capability
enables each application to tailor the server's operating mode to its own requirements.
SQL modes control aspects of server operation such as what SQL syntax MySQL should support and what kind of data validation
checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other
database servers.
General Information
17
You can set the default SQL mode by starting mysqld with the --sql-mode="mode_value" option. You can also change
the mode at runtime by setting the sql_mode system variable with a SET [GLOBAL|SESSION]
sql_mode='mode_value' statement.
For more information on setting the SQL mode, see Section 5.1.6, “Server SQL Modes”.
2.如果启动mysqld加上 -ansi相当于设置了sql_mode='ANSI'的全局参数,同时设置了SERIALIZABLE的事务隔离级别。
1.8.3. Running MySQL in ANSI Mode
You can tell mysqld to run in ANSI mode with the --ansi startup option. Running the server in ANSI mode is the same as
starting it with the following options:--transaction-isolation=SERIALIZABLE --sql-mode=ANSI
You can achieve the same effect at runtime by executing these two statements:
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL sql_mode = 'ANSI';You can see that setting the sql_mode system variable to 'ANSI' enables all SQL mode options that are relevant for ANSI
mode as follows:mysql> SET GLOBAL sql_mode='ANSI';
mysql> SELECT @@global.sql_mode;
-> 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI'Running the server in ANSI mode with --ansi is not quite the same as setting the SQL mode to 'ANSI'. The --ansi option affects the SQL mode and also sets the transaction isolation level. Setting the SQL mode to 'ANSI' has no effect on the isolation level.
3.mysql的update语句中如果引用某个字段，而这个字段也在本次更新之列，则会更新为最新的值.
练习：这里，如果写作如下会如何?
UPDATE t1 SET col2 = col1,col1 = col1 + 1;1.8.5.2. UPDATE Differences
If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column. The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.
UPDATE t1 SET col1 = col1 + 1, col2 = col1;
4. mysql 有许多特有的语法这些语法如果加上注释/*!*/则会在迁移到其他数据库产品的时候自动被忽略，但是在mysql里面则会执行
1.8.4. MySQL Extensions to Standard SQL
MySQL Server supports some extensions that you probably won't find in other SQL DBMSs. Be warned that if you use them, your
code won't be portable to other SQL servers. In some cases, you can write code that includes MySQL extensions, but is still portable,
by using comments of the following form:
/*! MySQL-specific code */
5.mysql将数据库对应到目录，将表对应到文件，所以，在Unix/Linux系统上，数据库和表可以区分大小写（windows上部区分？如果迁移呢？）在使用myISAM storage engine的时候，可以通过修改表对应的文件名来修改表名。
The following descriptions list MySQL extensions, organized by category.
• Organization of data on disk
MySQL Server maps each database to a directory under the MySQL data directory, and maps tables within a database to file
names in the database directory. This has a few implications:
• Database and table names are case sensitive in MySQL Server on operating systems that have case-sensitive file names
(such as most Unix systems). See Section 8.2.2, “Identifier Case Sensitivity”.
• You can use standard system commands to back up, rename, move, delete, and copy tables that are managed by the MyISAM
storage engine. For example, it is possible to rename a MyISAM table by renaming the .MYD, .MYI, and .frm files to
which the table corresponds. (Nevertheless, it is preferable to use RENAME TABLE or ALTER TABLE ... RENAME and
let the server rename the files.)
6.mysql 的权限在对象删除之后无法自动回收，需要手动回收
• There are several differences between the MySQL and standard SQL privilege systems. For example, in MySQL, privileges for
a table are not automatically revoked when you delete a table. You must explicitly issue a REVOKE statement to revoke privileges
for a table. For more information, see Section 12.4.1.5, “REVOKE Syntax”.
7.mysql允许创建事务表和非事务的表，非事务表不支持rollback，并且，非事务的表比起事务的表要快3-5倍，innodb是事务存储引擎，而MyISAM是非事务的存储引擎。 在使用非事务表的时候，如果在更新之间进行检查并且运行数据库一致性检查脚本可以自动修复或者告警数据不一致的问题，通常利用mysql Log可以完美修复数据而且不会有一致性损失。疑问是，如果一个事务的语句10条，执行到5条数据库故障了，启动后修复动作如何判断这5条是要还是不要？如果一致性很重要，mysql server对于非事务的表也提供了事务级别的可靠性---事先Lock table，加上read local lock则读不到其他session修改的数据，加上INSERT DELAYED可以在表被锁的情况下先插入到本地队列，只到表的锁释放。
However, even if you are new to the atomic operations paradigm, or more familiar with transactions, do consider the speed benefit that nontransactional tables can offer on the order of three to five times the speed of the fastest and most optimally tuned transactional tables
If you use nontransactional tables, MySQL Server in almost all cases enables you to resolve potential problems by including simple checks before updates and by running simple scripts that check the databases for inconsistencies and automatically repair or warn if such an inconsistency occurs. You can normally fix tables perfectly with no data integrity loss just by using the MySQL log or even adding one extra log.
In situations where integrity is of highest importance, MySQL Server offers transaction-level reliability and integrity even for nontransactional tables. If you lock tables with LOCK TABLES, all updates stall until integrity checks are made. If you obtain a READ LOCAL lock (as opposed to a write lock) for a table that enables concurrent inserts at the end of the table, reads are permitted, as are inserts by other clients. The newly inserted records are not be seen by the client that has the read lock until it releases the lock.
With INSERT DELAYED, you can write inserts that go into a local queue until the locks are released, without having the client wait for the insert to complete. See Section 7.10.3, “Concurrent Inserts”, and Section 12.2.5.2, “INSERT DELAYED Syntax”.
8.InnoDB engines支持外键约束保存和校验，但是目前MyISAM引擎尚不保存外键，将来可能会支持。
For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it. In the future, the implementation will be extended to store this information in the table specification file so that it may be retrieved by mysqldump and ODBC. At a later stage, foreign key constraints will be implemented for MyISAM General Information .
9.mysql支持两种注释，一种是/**/方式，一种是-- 方式，注意--后面要加上一个空格或者换行，这一点和oracle不同
Standard SQL uses the C syntax /* this is a comment */ for comments, and MySQL Server supports this syntax as well.
MySQL Server3.23.3 and up also supports a variant of the “--” comment style. That is, the “--” start-comment sequence must be followed by a space (or by a control character such as a newline).
10.InnoDB engine会在违反约束的情况下直接rollback,而其他engine则会停止处理后续语句，除非当前语句加上了 IGNORE 关键词，如此表示忽略违反主键或者唯一性约束的错误，继续处理下一行。
If you are using a transactional storage engine such as InnoDB, MySQL automatically rolls back the statement.If you are using a nontransactional storage engine, MySQL stops processing the statement at the row for which the error oc-General Information ocurred and leaves any remaining rows unprocessed.
MySQL supports an IGNORE keyword for INSERT, UPDATE, and so forth. If you use it, MySQL ignores primary-key or uniquekey violations and continues processing with the next row. See the section for the statement that you are using (Section 12.2.5,“INSERT Syntax”, Section 12.2.11, “UPDATE Syntax”, and so forth).
11.mysql默认情况下对于insert,update等错误数据采取容忍并且自动修正到最有可能的值的策略.过长字符串自动截断;非法数字保存为0;允许存入非法的日期值;如果单行insert遇到违反非空约束则报错，如果多行insert 遇到违反非空约束则保存为列定义数据类型的implict默认值----numeric:0,string:空串;insert 语句如果没有给值自动采用默认值，如果没有默认值就采用列定义数据类型的implict默认值。
If you try to store an out of range value into a numeric column, MySQL Server instead stores zero, the smallest possible value, or the largest possible value, whichever is closest to the invalid value.
• For strings, MySQL stores either the empty string or as much of the string as can be stored in the column.
• If you try to store a string that doesn't start with a number into a numeric column, MySQL Server stores 0.
• Invalid values for ENUM and SET columns are handled as described in Section 1.8.6.3, “ENUM and SET Constraints”.
• MySQL enables you to store certain incorrect date values into DATE and DATETIME columns (such as '2000-02-31' or
'2000-02-00'). The idea is that it is not the job of the SQL server to validate dates. If MySQL can store a date value and retrieve exactly the same value, MySQL stores it as given. If the date is totally wrong (outside the server's ability to store it), the special “zero” date value '0000-00-00' is stored in the column instead.
• If you try to store NULL into a column that doesn't take NULL values, an error occurs for single-row INSERT statements. For multiple-row INSERT statements or for INSERT INTO ... SELECT statements, MySQL Server stores the implicit default value for the column data type. In general, this is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. Implicit default values are discussed in Section 10.1.4, “Data Type Default Values”.
• If an INSERT statement specifies no value for a column, MySQL inserts its default value if the column definition includes an explicit DEFAULT clause. If the definition has no such DEFAULT clause, MySQL inserts the implicit default value for the column data type.
12.STRICT_TRANS_TABLES对于innodb engine来说就是违反约束则报错回滚;对于其他非事务引擎来说，如果执行的第一条语句就有错误，则报错，不执行，如果是后续语句有错误，则采取容忍和自动修正模式。STRICT_ALL_TABLES对于非事务引擎来说，即便是后续语句错误也报错并停止执行，这可能导致部分更新的情况发生。
SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';STRICT_TRANS_TABLES enables strict mode for transactional storage engines, and also to some extent for nontransactional engines.