个人MySQL杂记

（1）row_count 函数与 insert into... on duplicate key update

MySQL的官方释义如下：

In MySQL 5.7, ROW_COUNT() returns a value as follows:

• DDL statements: 0. This applies to statements such as CREATE TABLE or DROP TABLE.

• DML statements other than SELECT: The number of affected rows. This applies to statements such as UPDATE,INSERT, or DELETE (as before), but now also to statements such as ALTER TABLE and LOAD DATA INFILE.

• SELECT: -1 if the statement returns a result set, or the number of rows “affected” if it does not. For example, forSELECT * FROM t1, ROW_COUNT() returns -1. For SELECT * FROM t1 INTO OUTFILE 'file_name',ROW_COUNT() returns the number of rows written to the file.

• SIGNAL statements: 0.

For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE clause.

For REPLACE statements, the affected-rows value is 2 if the new row replaced an old row, because in this case, one row was inserted after the duplicate was deleted.

For INSERT ... ON DUPLICATE KEY UPDATE statements, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

The ROW_COUNT() value is similar to the value from the mysql_affected_rows() C API function and the row count that the mysql client displays following statement execution.

 默认情况下，insert into... on duplicate key update语句在成功insert后返回1，update返回2，表内数据完全没变化的话，返回0，如果定义了CLIENT_FOUND_ROWS 这一个标志位，那么完全没变化将会返回1

（2）外键设置：

外键约束的两个键必须类型完全一致，包括其约束条件（妈蛋的MySQL在这种时候居然不报什么原因，就光说无法创建外键。。。还好有错误号1215

（3）null在mysql排序中总是当做最小值处理。

（4）关于索引的几点不兼容：

MySQL官方文档如下：

Index Incompatibilities
In MySQL 5.6.3, the length limit for index prefix keys is increased from 767 bytes to 3072 bytes, for
InnoDB tables using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED. See Section 14.6.7, “Limits
on InnoDB Tables” for details. This change is also backported to MySQL 5.5.14. If you downgrade from
one of these releases or higher, to an earlier release with a lower length limit, the index prefix keys could
be truncated at 767 bytes or the downgrade could fail. This issue could only occur if the configuration
option innodb_large_prefix was enabled on the server being downgraded.


If you perform a binary upgrade without dumping and reloading tables, you cannot upgrade directly from
MySQL 4.1 to 5.1 or higher. This occurs due to an incompatible change in the MyISAM table index format
in MySQL 5.0. Upgrade from MySQL 4.1 to 5.0 and repair all MyISAM tables. Then upgrade from MySQL
5.0 to 5.1 and check and repair your tables.


Modifications to the handling of character sets or collations might change the character sort order, which
causes the ordering of entries in any index that uses an affected character set or collation to be incorrect.
Such changes result in several possible problems:
• Comparison results that differ from previous results
• Inability to find some index values due to misordered index entries
• Misordered ORDER BY results
• Tables that CHECK TABLE reports as being in need of repair


The solution to these problems is to rebuild any indexes that use an affected character set or collation,
either by dropping and re-creating the indexes, or by dumping and reloading the entire table. In some
cases, it is possible to alter affected columns to use a different collation. For information about rebuilding
indexes, see Section 2.10.4, “Rebuilding or Repairing Tables or Indexes”.


To check whether a table has indexes that must be rebuilt, consult the following list. It indicates which
versions of MySQL introduced character set or collation changes that require indexes to be rebuilt. Each
entry indicates the version in which the change occurred and the character sets or collations that the
change affects. If the change is associated with a particular bug report, the bug number is given.
The list applies both for binary upgrades and downgrades. For example, Bug #27877 was fixed in MySQL
5.1.24, so it applies to upgrades from versions older than 5.1.24 to 5.1.24 or newer, and to downgrades
from 5.1.24 or newer to versions older than 5.1.24.


In many cases, you can use CHECK TABLE ... FOR UPGRADE to identify tables for which index
rebuilding is required. It will report this message:
Table upgrade required.

Please do "REPAIR TABLE `tbl_name`" or dump/reload to fix it!
In these cases, you can also use mysqlcheck --check-upgrade or mysql_upgrade, which execute
CHECK TABLE. However, the use of CHECK TABLE applies only after upgrades, not downgrades. Also,
CHECK TABLE is not applicable to all storage engines. For details about which storage engines CHECK
TABLE supports, see Section 13.7.2.2, “CHECK TABLE Syntax”.

个人总结如下：

①5.6.3版本中，用了ROW_FORMAT=DYNAMIC或者ROW_FORMAT=COMPRESSED选项的InnoDB表，其索引的前缀字段的长度限制从767字节加到了3072字节（5.5.14也可以适用），所以如果你的服务器MySQL降级到5.5.14以下的版本，MySQL就有可能把索引的前缀字段截取或者降级失败。这个问题只会在配置项innodb_large_prefix开启时出现。

②