MySQL的一些tips总结

总结一下最近学到的MySQL的tips，做个备忘：

INSERT IGNORE 与 INSERT INTO的区别

INSERT IGNORE 与 INSERT INTO的区别就是：INSERT IGNORE会忽略数据库中已经存在 的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。INSERT INTO有无数据都插入，如果主键则不插入(而且报错)

insert语句一次可以插入多组值，每组值用一对圆括号括起来，用逗号分隔，如下：

insert into `news`(title, body, time) values('helloworld', 'body 1', now()), ('title 2', 'body 2', now());

下面通过代码说明之间的区别，如下：

create table testtb(
id int not null primary key,
name varchar(50),
age int
);

insert into testtb(id, name, age) values(1, "helloworld", 13);  //报错“ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'”
select * from testtb;

insert into testtb(id, name, age) values(1, "helloworld", 13);
select * from testtb;

insert ignore into testtb(id, name, age) values(1, "aa", 13);
select * from testtb;   //仍是 1 helloworld 13，因为id是主键，出现主键重复但使用了ignore则错误被忽略

replace into testtb(id, name, age) values(1, "aa", 12);
select * from testtb;   //数据变为1 aa 12

MySQL多表查询

SELECT * FROM table1,table2 WHERE table1.id=table2.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id;

笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN，或者使用','  如：
SELECT * FROM table1 CROSS JOIN table2
SELECT * FROM table1 JOIN table2
SELECT * FROM table1,table2
由于其返回的结果为被连接的两个数据表的乘积，因此当有WHERE, ON或USING条件的时候一般不建议使用，因为当数据表项目太多的时候，会非常慢。一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN

IP地址在MySQL数据库中的存储

echo ip2long('192.168.1.38');

C:>php -r "echo ip2long('219.143.8.242');"
-611383054

C:>php -r "echo ip2long('192.168.1.38');"
-1062731482

C:>php -r "echo ip2long('127.0.0.1');"
2130706433
==========
在32位的机子上，echo ip2long('192.168.1.38');由于超过32位的最大数，导致输出负数-1062731482。

有两种方法更新为正数：
$ip_long = bindec(decbin(ip2long($ip))); #验证OK！
或
$ip_long = = sprintf("%u", ip2long($ip));

mysql> select inet_aton('219.143.8.242');
+----------------------------+
| inet_aton('219.143.8.242') |
+----------------------------+
|                 3683584242 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa('3683584242');
+-------------------------+
| inet_ntoa('3683584242') |
+-------------------------+
| 219.143.8.242           |
+-------------------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa(3683584242);
+-----------------------+
| inet_ntoa(3683584242) |
+-----------------------+
| 219.143.8.242         |
+-----------------------+
1 row in set (0.00 sec)

mysql> select inet_aton('192.168.1.38');
+---------------------------+
| inet_aton('192.168.1.38') |
+---------------------------+
|                3232235814 |
+---------------------------+
1 row in set (0.00 sec)

select inet_aton('219.143.8.242');
select inet_ntoa('3683584242');
select inet_ntoa(3683584242);

两个函数都是根据：A*256*256*256+B*256*256+C*256+D的算法
192*256*256*256+168*256*256+1*256+38 = 3 232 235 814

反过来，从int转换为IP地址分别是php的long2ip()和mysql的INET_NTOA()。
mysql存储这个值是字段需要用int UNSIGNED。不用UNSIGNED的话，128以上的IP段就存储不了了。
传统的方法，创建varchar(15)，需要占用15个字节，而改时使用int只需要4字节，可以省一些字节。

PHP存入时：$ip = ip2long($ip);
MySQL取出时：SELECT INET_NTOA(ip) FROM table …
PHP取出时，多一步：$ip = long2ip($ip);

转换以前的数据：

1.把以前的varchar()数据转换为int型的SQL语句：
UPDATE `hx_table` SET ip = INET_ATON(ip) WHERE INET_ATON(ip) is NOT NULL

2.把字段更改为int型：
ALTER TABLE `hx_table` CHANGE `ip` `ip` INT UNSIGNED NOT NULL

程序做相应修改上传，完成。

因此一种是修改PHP程序，使其肯定存入正数。
另一种是将mysql的这个字段使用int，非UNSIGNED，使其可以存入负数。

参考链接：

待续……

16 10 月, 2014

admin

Database

mysql