mysql常见问题记录

  • INSERT ON DUPLICATE KEY UPDATE,影响的行数是多少?
  • REPLACE INTO
  • INSERT IGNORE INTO
  • select into from 和 insert into select 的用法和区别,mysql是否支持insert into select ?
  • 克隆表:create table ..as ..  && create table .. like .. 的区别
  • 关于唯一索引为null的情况
  • 如果批量插入的某个字段大于数据库定义的长度了,数据库会怎么处理?
  • 使用varchar 或char 做索引的话,如果用数字查的话,不能用到索引;如果用数字字符串就可以
 
》INSERT ON DUPLICATE KEY UPDATE
如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE
注意:
1:如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。
2:如果该表中,由多个唯一索引,需要特别注意,出现重复时则该语句只能更新其中一行记录
如:原有数据表中有数据:
 
UNIQUE KEY `uk_admin_name` (`admin_name`) USING BTREE,
UNIQUE KEY `uk_password` (`password`) USING BTREE
执行:
insert into tab_admin(`admin_name`, `password`) value('admin', 'admin888') on duplicate key
update last_ip = '202.0.0.1';
结果:
 
从上图可以看出:虽然admin,admin888和两行都匹配上了,但是只更新了一行数据,受影响的行: 2
至于为啥优先选择更新:admin_name这个唯一键? 原因:他会按照键的顺序来更新
 
结论:
1:
通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。
2:DUPLICATE 插入多行数据
 
 
 
》REPLACE INTO
原理:插入数据时,如果发现了重复记录,则系统自动先调用了DELETE删除这条记录,然后再用INSERT来插入这条记录,影响的行数为2行,如果没有发现,则直接插入,影响的行数是1行
注意:
REPLACE和INSERT ON DUPLICATE的区别,在于REPLACE会影响多条结果。比如在表中有超过一个的唯一索引。在这种情况下,REPLACE将考虑每一个唯一索引,并对每一个索引对应的重复记录都删除,然后插入这条新记录。假设有一个table1表,有3个字段a, b, c。它们都有一个唯一索引。
CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);
假设table1中已经有了3条记录
a b c
1 1 1
2 2 2
3 3 3
下面我们使用REPLACE语句向table1中插入一条记录。
REPLACE INTO table1(a, b, c) VALUES(1,2,3);
返回的结果如下
Query OK, 4 rows affected (0.00 sec)
在table1中的记录如下
a b c
1 2 3
我们可以看到,REPLACE将原先的3条记录都删除了,然后将(1, 2, 3)插入。
》使用ignore关键字
原理:如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用:
INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test9@163.com', '99999', '9999');
这样当有重复记录就会忽略,执行后返回受影响的行数为0
 
insert ignore into table(name)  select  name from table2
问:如果插入表的字段长度不满足select中查询的字段长度
答:字段会被截取(但是不会报错)
INSERT IGNORE
如果插入表的字段长度不满足select中查询的字段长度,则字段会被截取(但是不会报错
 
四、select into from 和 insert into select 的用法和区别
Insert into Table2(field1,field2,...) select value1,value2,... from Table1
注意:
要求目标表Table2必须存在,并且字段field,field2...也必须存在
由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,还可以插入常量
 
SELECT vale1, value2 into Table2 from Table1(在mysql中不支持)
注意:
要求目标表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中指定字段数据复制到Table2中 。 
 
 
》克隆表:create table ..as ..  && create table .. like .. 的区别
#原表
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(3) CHARACTER SET utf8mb4 NOT NULL,
`mobile` char(50) NOT NULL DEFAULT '',
`type` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name_mobile_type` (`name`,`mobile`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1
 
# 测试一
# create table test2 as select * from test;
CREATE TABLE `test2` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(3) CHARACTER SET utf8mb4 NOT NULL,
`mobile` char(50) CHARACTER SET latin1 NOT NULL DEFAULT '',
`type` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
效果:
1、完全copy了表的数据,但是对应新表缺少了key信息,以及自增列属性 auto_increment
 
# 测试一
# create table test3 like test;
CREATE TABLE `test3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(3) CHARACTER SET utf8mb4 NOT NULL,
`mobile` char(50) NOT NULL DEFAULT '',
`type` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name_mobile_type` (`name`,`mobile`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
效果:
从上面的表结构以及索引信息可以看到,表除了没有数据之外,结构被进行了完整克隆
 
结论:
复制表的时候要用create table like方式,复制后再用insert into select * from table 或load data方式加载数据
 
 
》关于唯一索引为null的情况
在MYSQL中UNIQUE索引将会对null字段失效,也就是说(a字段上建立唯一索引):
INSERT INTO `test` (`a`) VALUES (NULL);
是可以重复插入的(联合唯一索引也一样)
 
》如果插入的某个字段大于数据库定义的长度了,数据库会怎么处理?
1>如果数据库引擎是myisam,则数据库会截断后插入,不报错
2>如果数据库引擎是innodb,则数据库会报
  Data too long for column 'isp' at row 3
    并且如果是批量插入,则整个语句都不会插入成功!
 
》使用varchar 或char 做索引的话,如果用数字查的话,不能用到索引;如果用数字字符串就可以
 
 
原因:
数据库执行sql时,会有一个隐式的数据类型转换
 
总结:
字符类型存放数字,只有在查询时使用单引号的数字才会使用索引, 数字类型存放数字,查询时不管是否使用单引号,都会走索引
 
 
posted @ 2020-02-06 20:39  一束光  阅读(106)  评论(0编辑  收藏

友情链接

CFC4N