MySQL常用SQL

插入更新语句

问题说明：

开发中会有这样的场景，需要使用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 个字节，英文是一个字节