mysql 的一些 tips

最新推荐文章于 2024-02-04 15:17:38 发布

ROBOTECH_ER

最新推荐文章于 2024-02-04 15:17:38 发布

阅读量784

点赞数

分类专栏： mysql

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

auto-reconnect

timestamp vs datatime

(http://stackoverflow.com/questions/409286/datetime-vs-timestamp)

Timestamps in MySQL generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

datatime就是一个存储的时间值，而timestamp 可以自动更新，如果数据字段里有这个值，每次对数据记录的更新操作都会使这个值更新。

TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, and not for other types such as DATETIME.)

replace into vs ON DUPLICATE KEY UPDATE

这两个实现“存在则修改，不存在则插入的功能”，都是mysql的扩展不是标准的sql。

乍看之下的区别：

1、REPLACE发现重复的先删除再插入，如果记录有多个字段，在插入的时候如果有的字段没有赋值，那么新插入的记录这些字段为空，就是说原来的数据丢失了。
2、INSERT发现重复的是更新操作。在原有记录基础上，更新指定字段内容，其它的原有字段内容保留。所有列的值均取自在REPLACE语句中被指定的值。所有缺失的列被设置为各自的默认值，这和INSERT一样。您不能从当前行中引用值，也不能在新行中使用值。如果您使用一个例如“SET col_name = col_name + 1”的赋值，则对位于右侧的列名称的引用会被作为DEFAULT(col_name)处理。因此，该赋值相当于SET col_name = DEFAULT(col_name) + 1。

但实际上，MySQL的Replace into与Insert into...on duplicate key update...真正的不同之处(http://www.2cto.com/database/201303/193965.html)：

“……

总结从上面的测试结果看出，相同之处：
（1），没有key的时候，replace与insert .. on deplicate udpate相同。
（2），有key的时候，都保留主键值，并且auto_increment自动+1
不同之处：有key的时候，replace是delete老记录，而录入新的记录，所以原有的所有记录会被清除，这个时候，如果replace语句的字段不全的话，有些原有的比如例子中c字段的值会被自动填充为默认值。
而insert .. deplicate update则只执行update标记之后的sql，从表象上来看相当于一个简单的update语句。
但是实际上，根据我推测，如果是简单的update语句，auto_increment不会+1，应该也是先delete，再insert的操作，只是在insert的过程中保留除update后面字段以外的所有字段的值。

所以两者的区别只有一个，insert .. on deplicate udpate保留了所有字段的旧值，再覆盖然后一起insert进去，而replace没有保留旧值，直接删除再insert新值。
从底层执行效率上来讲，replace要比insert .. on deplicate update效率要高，但是在写replace的时候，字段要写全，防止老的字段数据被删除。

……”

显然的，发挥作用的前提是插入的字段里有primary key或者unique key。

这个功能也可以用先select 再insert或者update的方式实现，但是这么做效率较低。而且 replace into 和 ON DUPLICATE KEY UPDATE 也有（非常明显的）效率上的差别：Why “insert … on duplicate key update” May Be Slow, by Incurring Disk Seeks（http://www.tokutek.com/2010/07/why-insert-on-duplicate-key-update-may-be-slow-by-incurring-disk-seeks/）（这个网站上有着大量的关于性能优化的分析的文章）

注意问题：(ON DUPLICATE KEY UPDATE)插入时会与两条记录发生冲突，分别由主键和唯一索引引起。但最终只UPDATE了其中一条。这在手册中也说明了，有多个唯一索引（或者有键也有唯一索引）的情况下，不建议使用该语句。（mysql :INSERT INTO ON DUPLICATE KEY UPDATE 与 REPLACE INTO的区别:http://www.tanbo.name/html/552.html）

还有一个insert ignore语句，表示，如果中已经存在相同的记录，则忽略当前新数据。

一个简单的使用场景：

id都是 auto increment 的

int(4) vs int(11)

NULL vs MySQL空字符串

（from ： http://database.51cto.com/art/201011/235373.htm）

MySQL空字符串和NULL值我们都经常会见到，但是这二者并不是一个概念，下面就为您介绍NULL与MySQL空字符串的区别，供您参考。

对于SQL的新手，NULL值的概念常常会造成混淆，他们常认为NULL与MySQL空字符串是相同的事。情况并非如此。例如，下述语句是完全不同的：
MySQL> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');
这两条语句均会将值插入phone（电话）列，但第1条语句插入的是NULL值，第2条语句插入的是空字符串。第1种情况的含义可被解释为“电话号码未知”，而第2种情况的含义可被解释为“该人员没有电话，因此没有电话号码”。

为了进行NULL处理，可使用IS NULL和IS NOT NULL操作符以及IFNULL()函数。

在SQL中，NULL值与任何其它值的比较（即使是NULL）永远不会为“真”。包含NULL的表达式总是会导出NULL值，除非在关于操作符的文档中以及表达式的函数中作了其他规定。下述示例中的所有列均返回NULL：mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);
如果打算搜索列值为NULL的列，不能使用expr = 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 = '';
更多信息和示例：
如果你正在使用MyISAM、InnoDB、BDB、或MEMORY存储引擎，能够在可能具有NULL值的列上增加1条索引。如不然，必须声明索引列为NOT NULL，而且不能将NULL插入到列中。

用LOAD DATA INFILE读取数据时，对于空的或丢失的列，将用''更新它们。如果希望在列中具有NULL值，应在数据文件中使用\N。在某些情况下，也可以使用文字性单词“NULL”。

使用DISTINCT、GROUP BY或ORDER BY时，所有NULL值将被视为等同的。

使用ORDER BY时，首先将显示NULL值，如果指定了DESC按降序排列，NULL值将最后显示。

对于聚合（累计）函数，如COUNT()、MIN()和SUM()，将忽略NULL值。对此的例外是COUNT(*)，它将计数行而不是单独的列值。例如，下述语句产生两个计数。首先计数表中的行数，其次计数age列中的非NULL值数目：mysql> SELECT COUNT(*), COUNT(age) FROM person;
对于某些列类型，MySQL将对NULL值进行特殊处理。如果将NULL插入TIMESTAMP列，将插入当前日期和时间。如果将NULL插入具有AUTO_INCREMENT属性的整数列，将插入序列中的下一个编号。

（ps：使用ruby mysql2从数据库中读取的时候，NULL得到的结果都是nil，空字符串读取到的是“”，显然的。）

ENUM VS SET

mysql set类型和enum类型：(http://blog.chinaunix.net/uid-167175-id-3498431.html)

这两个都是mysql的特色字段，两个都是字串字段。不同的是set可以取多个值,enum只能取一个值。

如果一个 ENUM 被声明为NULL，NULL 也是该列的一个合法值，并且该列的缺省值也将为NULL 。如果一个ENUM 被声明为NOT NULL，该列的缺省值将是该列表所允许值的第一个成员（索引是1，不是0）。
每个枚举值均有一个索引值：
在列说明中列表值所允许的成员值被从 1 开始编号。
空字符串错误值的索引值为 0。这就意味着，你可以使用下面所示的 SELECT 语句找出被赋于无效ENUM值的记录行。
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
NULL 值的索引值为NULL。
例如，指定为 ENUM("one", "two", "three") 的一个列，可以有下面所显示的任一值。每个值的索引值也如下所示：
值索引值
NULL NULL
"" 0
"one" 1
"two" 2
"three" 3
测试时发现''时，用0来表示查不到数据。换个枚举最大可以有 65535 个成员值。

实际使用时，很少用索引，而是直接用相应的字串：

mysql> SELECT * FROM tbl_name WHERE enum_col = “three”;

key:primary key, unique key

mysql中，key和index是同义词，建立key的同时也就自动建立了索引。

每个表只能由一个主键，可以是包含多个字段的联合主键。unique key可以有多个。

unique key的作用是防止重复值的插入。

重新排列自增长字段

反复的插入、删除后，数据库的自增长字段会变得很不连续。一种解决的方法是删除后重新插入：

ALTER TABLE tablename DROP id;
ALTER TABLE tablename ADD id INT NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST

动手之前最好先备份一下。