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,使其可以存入负数。

参考链接:


待续……


《“MySQL的一些tips总结”》 有 8 条评论

  1. 在MySQL中查询某一个时间区间内的记录(between…and)
    `
    select * from hockey_stats where game_date between ‘2016-09-11 00:00:00’ and ‘2016-10-11 23:59:00’ order by game_date desc;

    select * from hockey_stats where game_date < '2016-01-01 00:00:00';
    `

  2. MySQL字段如何设置为「当前/当天」的时间?
    https://stackoverflow.com/questions/19246309/how-to-get-current-date-time-in-mysql
    `
    — 创建时自己填充当前时间 DEFAULT CURRENT_TIMESTAMP
    CREATE TABLE `servers`
    (
    id int(11) NOT NULL PRIMARY KEY auto_increment,
    …,
    date_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    — 插入时用当前时间 NOW()
    INSERT INTO servers (server_name, online_status, exchange, disk_space, network_shares)
    VALUES(‘m1′,’ONLINE’,’exchange’,’disk_space’,’network_shares’, NOW())
    `
    https://stackoverflow.com/questions/20461030/current-date-curdate-not-working-as-default-date-value

  3. MySQL中如何删除「特定时间之前」的数据?
    https://stackoverflow.com/questions/8359457/deleting-records-before-a-certain-date
    `
    — 固定时间之前
    DELETE FROM table_name WHERE column_name < '2011-09-21 08:21:22';

    — 删除 1 天之前的数据
    delete from table_name where column_name < DATE_SUB(NOW() , INTERVAL 1 DAY)

    — 删除 6 月之前的数据
    delete from table_name where column_name < DATE_SUB(NOW() , INTERVAL 6 MONTH)
    `
    https://dba.stackexchange.com/questions/42892/drop-tables-where-the-last-update-is-over-1-week-ago

  4. MySQL中将多列的组合设置为主键的语法
    https://dba.stackexchange.com/questions/57548/how-to-set-up-multiple-fields-as-primary-key-in-mysql
    https://stackoverflow.com/questions/2642778/multi-column-primary-key-in-mysql-5
    https://dev.mysql.com/doc/refman/5.6/en/create-table.html
    `
    CREATE TABLE yourtable
    (
    employeeid INT,
    blahblah VARCHAR(255),
    blahblah2 VARCHAR(255),
    recordmonth DATE,
    recodrdyear DATE,
    PRIMARY KEY (employeeid, recordmonth, recodrdyear)
    )
    `

  5. [慢查优化]联表查询注意谁是驱动表 & 你搞不清楚谁join谁更好时请放手让mysql自行判定
    https://www.cnblogs.com/zhengyun_ustc/p/slowquery1.html

    mysql驱动表与被驱动表及join优化
    https://blog.csdn.net/qq_20891495/article/details/93744495
    `
    # 驱动表与被驱动表
    先了解在join连接时哪个表是驱动表,哪个表是被驱动表:
    1.当使用left join时,左表是驱动表,右表是被驱动表
    2.当使用right join时,右表时驱动表,左表是驱动表
    3.当使用join时,mysql会选择数据量比较小的表作为驱动表,大表作为被驱动表

    在sql优化中,永远是以小表驱动大表。

    在以小表驱动大表的情况下,再给大表建立索引会大大提高执行速度。
    `

回复 hi 取消回复

您的电子邮箱地址不会被公开。 必填项已用*标注