插入更新语句
问题说明:
开发中会有这样的场景,需要使用insert语句来插入一些数据,但是有的时候插入的数据已经存在,我们就需要将原来的数据按照插入的数据进行更新。简单来说,就是当插入的数据不存在时,进行数据的插入;当数据存在时,则进行更新
解决方式:
使用SQL语句来进行解决
前提:要为表中的某一个或者几个字段建立唯一索引,也就是说,当这几个字段的值在数据库中存在时,则更新数据;反之,则插入数据。
场景描述
1.首先创建表
CREATE TABLE `test_table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`var1` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`var2` tinyint(1) NOT NULL DEFAULT '0',
`var3` varchar(100) character set utf8 default NULL,
`value1` int(11) NOT NULL DEFAULT '1',
`value2` int(11) NULL DEFAULT NULL,
`value3` int(5) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE INDEX `index_var` (`var1`, `var2`, `var3`)
) ENGINE=MyISAM DEFAULT CHARACTER SET=latin1 AUTO_INCREMENT=1;
先创建一个表,并将表中var1、var2、var3这三个字段组成一个唯一索引
2.进行测试
执行如下insert into table on duplicate key update语句
INSERT INTO `test_table`
(`var1`, `var2`, `var3`, `value1`, `value2`, `value3`) VALUES
('abcd', 2, 'xyz', 1, 2, 3)
ON DUPLICATE KEY UPDATE value1=2,value2=3,value3=5;
第一次执行时,首先会在数据库中插入数据,结果如下
再次执行这条语句,结果如下
结果很明显,并没有新插入一条数据,而是在原有数据的基础上进行了更新,更新的字段为UPDATE后面的字段。
再次进行试验
INSERT INTO `test_table`
(`var1`, `var2`, `var3`, `value1`, `value2`, `value3`) VALUES
('abcd', 5, 'xyz', 1, 2, 3)
ON DUPLICATE KEY UPDATE value1=2,value2=3,value3=5;
改变唯一索引(var1、var2、var3)这三个字段中的任意一个字段,或者三个字段都改变,则会往数据库中新插入一条数据
结论
使用该条语句时,如果唯一索引的值被改变,那么就会向数据库中新插入数据,如果唯一索引的值没有改变,则会修改原来数据的值,修改值为UPDATE后面指定的值
2.1.再次测试
如果在增加一个唯一索引,这个唯一索引由var1、var2、var3、var4这四个字段组成呢?
//先给表增加一列
alter table test_table add var4 varchar(100)
//创建唯一索引
alter table test_table add UNIQUE INDEX var1_4 (var1,var2,var3,var4)
(1)这个时候,在进行测试,如果改变var1、var2、var3之中的任意一个值,那么毫无疑问会新插入数据,因为这满足了修改了唯一索引,但是,如果该改变了var4呢,经过实验后,发现不起作用,也就是说,它只会将var4当作普通字段,在var1、var2、var3不变的情况下,不会新插入数据,只会修改原来的数据;
INSERT INTO `test_table`
(`var1`, `var2`, `var3`,`var4`, `value1`, `value2`, `value3`) VALUES
('abcd', 5, 'xyz','k', 1, 2, 3)
ON DUPLICATE KEY UPDATE value1=2,value2=3,value3=5;
这个结果,没有在数据库中新插入数据,也没有更新原来的数据,因为var4的值没有写在UPDATE后面
(2)进一步测试,改变var1、var2、var3、var4创建唯一索引的顺序
alter table test_table add UNIQUE INDEX var4_1 (var4,var2,var3,var1);
经过测试发现,还是跟上面结果一样,将var4当成了普通字段,知修改var4的情况下,不会插入数据,只会修改数据(注意:修改的数据一律是UPDATE后面的数据,在不插入的情况下var4的值不会改变依然是空值,因为var4字段没有写在UPDATE后面)
(3)最终测试,将第一次那三个字段建立的索引删除掉,现在还有两个四个字段组成的唯一索引,这个时候,只要是修改var1-var4任意一个字段的值,都会进行数据的新增,var1-var4的值没有改变,只会修改数据
最终结论
如果有多个字段组成的唯一索引,修改其中一个值都会导致新数据的插入;如果新创建了一个唯一索引包含了原来索引的所有字段(在字段层面可以看作包含了原来索引的所有字段,也就是原来索引的父集),那么后面这个新创建的索引比前面那个索引多出的字段,跟普通字段一样不会起到任何作用。如果想要创建唯一索引,并且这个新建索引的所有字段,都包含在第一个唯一索引里面(在字段层面可以看作为第一个索引的子集),这个时候,就会提示创建索引失败。
3.应用场景
主要应用在:
有些字段没有更新时,则更新数据;例如在在主键id不变的情况下,使用该条sql插入的数据都会变成更新,如果主键有变化,则会新增数据。(因为主键也是唯一键,这条sql语句只会对唯一键索引起作用)
注意:
在mysql中执行批量插入语句时,如果有一条失败,则会全部失败,这是由mysql自身保证的
索引相关
1.创建多个字段的联合索引
出现问题:
“Specified key was too long; max key length is xxx bytes”
问题描述:
需要给多个字段共同创建一个唯一索引,因为字段定义的过长的缘故,导致创建联合索引的时候,出现错误
解决思路:
修改字段长度之后,重新尝试建立联合索引
例如:
ALTER TABLE `ka_in` CHANGE mi_wh_name mi_wh_name VARCHAR(96) NOT NULL DEFAULT '' COMMENT '小米仓库名';
ALTER TABLE `ka_in` DROP index UK_ka_in_key,
ADD UNIQUE index UK_ka_in_key (`dc_no`,`wh_no`,`company_code`,`sku_code`,`mi_wh_name`,`create_time`);
2.varchar(10)跟varchar(100)区别?
磁盘上存储的字段值是根据实际的字段值来存储的,varchar(10)跟varchar(100)并无差别;但是内存中使用该字段(where字句、索引、其他…)时,会根据定义的大小来为其分配内存空间。所以在建表时,一定要设置一个合理的值,不能过大
sql
1.常用sql
添加字段
ALTER TABLE ka_enter ADD COLUMN 字段名 VARCHAR(16) NOT NULL DEFAULT '' COMMENT '字段说明' after 字段;
示例:
ALTER TABLE ka_enter ADD COLUMN goods_type_name VARCHAR(16) NOT NULL DEFAULT '' COMMENT '商品件型';
说明:after可以指定把新增加的字段放在在某个已有字段的后面,如果不加after,则默认新加入的字段在表的末尾
修改某个字段
ALTER TABLE ka_in CHANGE 修改前的字段 修改后的字段 VARCHAR(96) NOT NULL DEFAULT '' COMMENT '小米仓库名';
示例:
ALTER TABLE ka_in CHANGE mi_wh_name mi_wh_name VARCHAR(96) NOT NULL DEFAULT '' COMMENT '小米仓库名';
修改索引
ALTER TABLE ka_store DROP index 修改前的索引名,
ADD UNIQUE index 修改后的索引名(`组成索引的字段1`,`组成索引的字段2`,...);
示例:
ALTER TABLE ka_store DROP index UK_ka_store_key,
ADD UNIQUE index UK_ka_store_key(`dc_no`,`wh_no`,`company_code`,`mi_wh_name`,`create_time`);
case when语法
select name,case type
when 63 then '其他'
when 631 then '机器内部'
when 632 then '机器外'
end as 照片类型
from my_picture_info where business_code="111111111"
一些特殊sql
显示当前数据库:show databases;
显示当前数据库中所有的表:show tables;
模糊查询当前数据库中的某张表:show tables like "%表名关键字%"
查看当前数据库中所有的表:SELECT table_name,table_type,table_schema FROM information_schema.TABLES
显示表结构:desc 表名
显示创建表的语句(包括了表的索引啥的,很详细):show create table 表名
查看某一张表的索引:show index from 表名
模糊查询数据库、表、字段:select table_schema, table_name, column_name, column_comment from information_schema.columns where column_name like '%fac%' and table_schema = 'spc';
根据字段名称查询该字段所在的表:SELECT table_name FROM information_schema.columns WHERE TABLE_SCHEMA = '数据库' AND COLUMN_NAME='列名';
查询某张表所在的数据库:SELECT table_schema FROM information_schema.tables WHERE TABLE_NAME = 'table_name';
字段类型
背景:如果在MySQL中定义了Boolean类型的数据,会被MySQL自动转换成tinyint
给表增加Booleanl类型的字段:
alter table mcs_material_borrow_return
add auto_create boolean default null comment '是否为系统自动创建的借用单',
add manufacture_code varchar(64) default null comment '厂家系统单号';
实际表的结构:
`auto_create` tinyint(1) DEFAULT NULL COMMENT '是否为系统自动创建的借用单',
应用:
如果 插入的是true,那么mysql会自动变成1,如果是false,mysql会自动变成0
字段类型长度
在定义字段类型长度时,需要注意:
id int 跟id int(1):在内存占用(都是4个字节)以及磁盘存储上并无区别,只是查询显示的时候会有区别
varchar(10)跟varchar(1000):这两个在内存占用上是有区别的,varchar(1000)要比varchar(10)占用内存要大,
但是实际存储到磁盘上时,两个占用磁盘的大小是一样的
如果想要在mysql中存储json类型的数据,在高版本中可以使用json数据类型;在低版本中,直接使用varchar类型就行了,不用使用text类型;varchar类型能存储的最大长度(65536字节)足够满足一般的业务需要了
1.使用utf-8字符编码集varchar最大长度是(超过255个字节会有2字节的额外占用空间开销,所以减2,如果是255以下,则减1)。
注意:英文字符不管是什么编码(utf-8、gbk、utf8mb4),都是占用一个字节。65536字节,只存储英文字符的能存储65536个字符,如果全是中文(utf-8编码),一个汉字占用3个字节,则可以存储(65535-2)/3=21844个字符
编码相关
UTF-8
UTF-8 Unicode Transformation Format-8bit。是用以解决国际上字符的一种多字节编码。它对英文使用 8 位(即一个字节) ,中文使用 24 位(三个字节)来编码。UTF-8包含全世界所有国家需要用到的字符,是国际编码,通用性强。UTF-8编码的文字可以在各国支持 UTF8 字符集额的浏览器上显示。
GBK
GBK 是国家标准 GB2312 基础上扩容后兼容 GB2312 的标准。GBK的文字编码是用双字节来表示的,即不论中、英文字符均使用双字节来表示,为了区分中文,将其最高位都设定成 1。GBK包含全部中文字符,是国家编码,通用性比 UTF8 差,不过 UTF8 占用的数据库比GBK大。
UTF8MB4(utf8mb4)
MySql 5.5 之前,UTF8 编码只支持 1-3 个字节,只支持 BMP 这部分的 unicode 编码区,BMP 是从哪到哪?
戳这里 基本就是 0000 ~ FFFF 这一区。
从 MySQL 5.5 开始,可支持 4 个字节 UTF 编码 utf8mb4,一个字符最多能有 4 字节,所以能支持更多的字符集。
utf8mb4 is a superset of utf8
tf8mb4 兼容 utf8,且比 utf8 能表示更多的字符。
至于什么时候用,看你做的什么项目了。。。
在做移动应用时,会遇到IOS用户在文本的区域输入emoji表情,如果不做一定处理,就会导致插入数据库异常。
总结:
UTF-8:一个汉字 = 3 个字节,英文是一个字节
GBK: 一个汉字 = 2 个字节,英文是一个字节