分类目录归档:mysql

mysql默认值

如果一个字段中没有指定DEFAULT修饰符,MySQL会依据这个字段是NULL还是NOT NULL自动设置默认值。如果指定字段可以为NULL,则MySQL为其设置默认值为NULL。如果是NOT NULL字段,MySQL对于数值类型插入0,字符串类型插入空字符串,时间戳类型插入当前日期和时间,ENUM类型插入枚举组的第一条。

mysql优化记录

两组结果一样,写法相近的mysql语句,但性能相差接近100倍,是何缘故。
EXPLAIN SELECT * FROM wareprice_01 wp WHERE id=(SELECT MAX(id) FROM wareprice_01 p WHERE p.wareid IN (44166981760,43770830980,43770832630,44976304870,43994294220,38585515990,45140073950) AND wp.wareid = p.wareid);

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY wp ALL \N \N \N \N 90770 Using where
2 DEPENDENT SUBQUERY p ref wareidp wareidp 8 tmware.wp.wareid 1 Using where

EXPLAIN SELECT * FROM wareprice_01 wp WHERE id=(SELECT MAX(id) FROM wareprice_01 p WHERE wp.wareid = p.wareid) AND wp.wareid IN (44166981760,43770830980,43770832630,44976304870,43994294220,38585515990,45140073950);

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY wp range wareidp wareidp 8 \N 8 Using where
2 DEPENDENT SUBQUERY p ref wareidp wareidp 8 tmware.wp.wareid 1