MySQL中几种插入和批量语句实例详解

目录
  • 前言
  • 1.insert ignore into
  • 2.on duplicate key update
  • 3.replace into
  • 4.insert if not exists
  • 5.批量插入数据
  • 6.批量更新
  • 总结

前言

最常见的方式就是为字段设置主键或唯一索引，当插入重复数据时，抛出错误，程序终止，但这会给后续处理带来麻烦，因此需要对插入语句做特殊处理，尽量避开或忽略异常，下面我简单介绍一下，感兴趣的朋友可以尝试一下：

这里为了方便演示，我新建了一个user测试表，主要有id，username，sex，address这4个字段，其中主键为id（自增），同时对username字段设置了唯一索引（idx_username）

1.insert ignore into

即插入数据时，如果数据存在，则忽略此次插入，前提条件是插入的数据字段设置了主键或唯一索引，测试SQL语句如下，当插入本条数据时，MySQL数据库会首先检索已有数据（也就是idx_username索引），如果存在，则忽略本次插入，如果不存在，则正常插入数据：

INSERT IGNORE INTO user (username,sex,address) VALUES ('hanpang','boy','HongKong')

2.on duplicate key update

即插入数据时，如果数据存在，则执行更新操作，前提条件同上，也是插入的数据字段设置了主键或唯一索引，测试SQL语句如下，当插入本条记录时，MySQL数据库会首先检索已有数据（idx_username索引），如果存在，则执行update更新操作，如果不存在，则直接插入：

INSERT IGNORE INTO user (username,sex,address) VALUES ('hanpang','boy','HongKong')
on duplicate key update
SEX='boy',address='HongKong'

3.replace into

即插入数据时，如果数据存在，则删除再插入，前提条件同上，插入的数据字段需要设置主键或唯一索引，测试SQL语句如下，当插入本条记录时，MySQL数据库会首先检索已有数据（idx_username索引），如果存在，则先删除旧数据，然后再插入，如果不存在，则直接插入：

REPLACE INTO user (username,sex,address) VALUES ('hanpang','boy','HongKong')

4.insert if not exists

即 insert into … select … where not exist ... ，这种方式适合于插入的数据字段没有设置主键或唯一索引，当插入一条数据时，首先判断MySQL数据库中是否存在这条数据，如果不存在，则正常插入，如果存在，则忽略：

INSERT  INTO user (username,sex,address)
SELECT 'hanpang','boy','HongKong' FROM user
WHERE NOT EXISTS (SELECT username FROM user WHERE username='hanpang')

5.批量插入数据

上述的插入语句，是可以使用批量插入语句，表数据结构：

CREATE TABLE example (
    example_id INT NOT NULL,
    name VARCHAR( 50 ) NOT NULL,
    value VARCHAR( 50 ) NOT NULL,
    other_value VARCHAR( 50 ) NOT NULL
)

个人习惯使用这种批量操作方式，进行SQL语句拼接，但是当你的字符串太长（百万数据）的时候，需要你对mysql设置指令：

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

实际开发中，我们更加喜欢使用通过程序代码进行批量添加操作（使用事务提交，批量插入数据库），使用上述的方式在插入测试数据或者其他低要求时比较合适，速度确实快。

6.批量更新

（1）replace into 批量更新(记得要有主键或者索引)

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

（2）insert into ...on duplicate key update批量更新

使用INSERT的时候 有表T(id,A,B,C,D)

插入的时候希望通过A,B索引唯一记录 ，有重复的时候更新C,D

INSERT INTO T(A,B,C,D) VALUES (a,b,c,d) ON DUPLICATE KEY UPDATE C=C+1，D=d

（3）使用mysql 自带的语句构建批量更新

UPDATE yoiurtable
	SET dingdan = CASE id
		WHEN 1 THEN 3
		WHEN 2 THEN 4
		WHEN 3 THEN 5
		END
WHERE id IN (1,2,3)

（4）创建临时表，先更新临时表，然后从临时表中update

create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values  (0,'gone'), (1,'xx'),...(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

总结

到此这篇关于MySQL中几种插入和批量语句的文章就介绍到这了,更多相关MySQL插入和批量语句内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL批量插入和唯一索引问题的解决方法

    MySQL批量插入和唯一索引问题的解决方法

    MySQL批量插入问题 在开发项目时,因为有一些旧系统的基础数据需要提前导入,所以我在导入时做了批量导入操作 ,但是因为MySQL中的一次可接受的SQL语句大小受限制所以我每次批量虽然只有500条,但依然无法插入,这个时候代码报错如下: nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576). You can change this va

  • mysql大批量插入数据的4种方法示例

    前言 本文主要给大家介绍了关于mysql大批量插入数据的4种方法,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧 方法一:循环插入 这个也是最普通的方式,如果数据量不是很大,可以使用,但是每次都要消耗连接数据库的资源. 大致思维如下 (我这里写伪代码,具体编写可以结合自己的业务逻辑或者框架语法编写) for($i=1;$i<=100;$i++){ $sql = 'insert...............'; //querysql } foreach($arr as $key =

  • MySQL实现批量插入以优化性能的教程

    MySQL实现批量插入以优化性能的教程

    对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长.特别像报表系统,每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久.因此,优化数据库插入性能是很有意义的. 经过对MySQL innodb的一些性能测试,发现一些可以提高insert效率的方法,供大家参考参考. 1. 一条SQL语句插入多条数据. 常用的插入语句如: INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VAL

  • MySQL批量插入遇上唯一索引避免方法

    MySQL批量插入遇上唯一索引避免方法

    一.背景 以前使用SQL Server进行表分区的时候就碰到很多关于唯一索引的问题:Step8:SQL Server 当表分区遇上唯一约束,没想到在MySQL的分区中一样会遇到这样的问题:MySQL表分区实战. 今天我们来了解MySQL唯一索引的一些知识:包括如何创建,如何批量插入,还有一些技巧上SQL: 这些问题的根源在什么地方?有什么共同点?MySQL中也有分区对齐的概念?唯一索引是在很多系统中都会出现的要求,有什么办法可以避免?它对性能的影响有多大? 二.过程 (一) 导入差异数据,忽略重

  • MySQL如何快速批量插入1000w条数据

    MySQL如何快速批量插入1000w条数据

    听说有个面试题是: 如何快速向mysql中插入1000w条数据? 我私下试了一下, 发现插入10000条数据用了0.9s, 插入10w条数据用了4.7s, 插入100w条数据用了58s左右,1000w条数据,我的笔记本吭哧了5分钟,自己停了, 心中1000w只草泥马呼啸而过,我用的是下面的代码: -- 进入数据库 use test; -- 显示所有表 show tables; -- 创建majors表 create table majors(id int, major varchar(255))

  • MySQL批量插入数据脚本

    MySQL批量插入数据脚本 #!/bin/bash i=1; MAX_INSERT_ROW_COUNT=$1; while [ $i -le $MAX_INSERT_ROW_COUNT ] do mysql -uroot -proot dbname -e "insert into tablename (name,age,createTime) values ('HELLO$i',$i % 99,NOW());" d=$(date +%M-%d\ %H\:%m\:%S) echo &qu

  • MySQL批量SQL插入性能优化详解

    MySQL批量SQL插入性能优化详解

    对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长.特别像报表系统,每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久.因此,优化数据库插入性能是很有意义的. 经过对MySQL innodb的一些性能测试,发现一些可以提高insert效率的方法,供大家参考参考. 1. 一条SQL语句插入多条数据. 常用的插入语句如: INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VAL

  • MYSQL批量插入数据的实现代码第1/3页

    @echo off cls set CLASSPATH=..\api\jogre.jar set CLASSPATH=%CLASSPATH%;. set CLASSPATH=%CLASSPATH%;classes set CLASSPATH=%CLASSPATH%;lib\dom4j.jar java org.jogre.server.JogreServer 建表 复制代码 代码如下: create database con_test; use con_test; create table te

  • MySQL中几种插入和批量语句实例详解

    目录 前言 1.insert ignore into 2.on duplicate key update 3.replace into 4.insert if not exists 5.批量插入数据 6.批量更新 总结 前言 最常见的方式就是为字段设置主键或唯一索引,当插入重复数据时,抛出错误,程序终止,但这会给后续处理带来麻烦,因此需要对插入语句做特殊处理,尽量避开或忽略异常,下面我简单介绍一下,感兴趣的朋友可以尝试一下: 这里为了方便演示,我新建了一个user测试表,主要有id,userna

  • MySQL中增删改查操作与常见陷阱详解

    MySQL中增删改查操作与常见陷阱详解

    目录 本文导读 一.MySQL的增删改查 1.insert语句 2.delete语句 3.update语句原理 4.select 二.15种MySQL数据操作语句 1.REPLACE语句 2.CALL语句 3.TABLE语句 4.WITH语句 三.MySQL查询陷阱 总结 本文导读 本文作为MySQL系列第二篇文章,详细讲解了MySQL的增删改查的语句.语义和一些我们经常在开发工作中暴露的问题,MySQL的增删改查又叫数据操作语句,本文有讲些了一些常用的数据操作语句,select语句后续将作为一

  • 浅谈MySQL中授权(grant)和撤销授权(revoke)用法详解

    MySQL 赋予用户权限命令的简单格式可概括为: grant 权限 on 数据库对象 to 用户 一.grant 普通数据用户,查询.插入.更新.删除 数据库中所有表数据的权利 grant select on testdb.* to common_user@'%' grant insert on testdb.* to common_user@'%' grant update on testdb.* to common_user@'%' grant delete on testdb.* to c

  • MySQL中Decimal类型和Float Double的区别(详解)

    MySQL中存在float,double等非标准数据类型,也有decimal这种标准数据类型. 其区别在于,float,double等非标准类型,在DB中保存的是近似值,而Decimal则以字符串的形式保存数值. float,double类型是可以存浮点数(即小数类型),但是float有个坏处,当你给定的数据是整数的时候,那么它就以整数给你处理.这样我们在存取货币值的时候自然遇到问题,我的default值为:0.00而实际存储是0,同样我存取货币为12.00,实际存储是12. 幸好mysql提供

  • MySQL中int(10)和int(11)的区别详解

    目录 一.背景 二.MySQL整数类型 总结: 一.背景 在创建数据库表的时候,我们经常会用到int(x)来定义一个字段的类型,一直误以为这里的x表示存储数字的长度. 其实大错特错,这里的 x 指的是 最大显示宽度(最大有效显示宽度是255),且显示宽度与存储大小或类型包含的值的范围无关. 二.MySQL整数类型 类型 字节 取值范围 显示宽度 tinyint 1 -128 ~ 127 4 smallint 2 -32768 ~ 32767 6 mediumint 3 -8388608 ~ 83

  • MySql中的IFNULL、NULLIF和ISNULL用法详解

    今天用到了MySql里的isnull才发现他和MSSQL里的还是有点区别,现在简单总结一下: mysql中isnull,ifnull,nullif的用法如下: isnull(expr) 的用法: 如expr 为null,那么isnull() 的返回值为 1,否则返回值为 0. mysql> select isnull(1+1); -> 0 mysql> select isnull(1/0); -> 1 使用= 的null 值对比通常是错误的. isnull() 函数同 is nul

  • thinkphp中的多表关联查询的实例详解

    thinkphp中的多表关联查询的实例详解 在进行后端管理系统的编程的时候一般会使用框架来进行页面的快速搭建,我最近使用比较多的就是thinkphp框架,thinkphp框架的应用其实就是把前端和后端进行分割管理,前端用户登录查询系统放在thinkphp中的home文件夹中进行管理,后端管理系统放在thinkphp中的admin文件夹中进行管理.对了,在使用thinkphp框架的时候是是要用到mvc架构的,mvc架构就是model(数据模型).view(视图).controller(控制器)的结

  • JavaScript学习小结之被嫌弃的eval函数和with语句实例详解

    前面的话 eval和with经常被嫌弃,好像它们的存在就是错误.在CSS中,表格被嫌弃,在网页中只是用表格来展示数据,而不是做布局,都可能被斥为不规范,矫枉过正.那关于eval和with到底是什么情况呢?本文将详细介绍eval()函数和with语句 eval 定义 eval()是一个全局函数,javascript通过eval()来解释运行由javascript源代码组成的字符串 var result = eval('3+2'); console.log(result,typeof result)

  • mysql获取分组后每组的最大值实例详解

     mysql获取分组后每组的最大值实例详解 1. 测试数据库表如下: create table test ( `id` int not null auto_increment, `name` varchar(20) not null default '', `score` int not null default 0, primary key(`id`) )engine=InnoDB CHARSET=UTF8; 2. 插入如下数据: mysql> select * from test; +---

  • java 中mongodb的各种操作查询的实例详解

    java 中mongodb的各种操作查询的实例详解 一. 常用查询: 1. 查询一条数据:(多用于保存时判断db中是否已有当前数据,这里 is  精确匹配,模糊匹配 使用regex...) public PageUrl getByUrl(String url) { return findOne(new Query(Criteria.where("url").is(url)),PageUrl.class); } 2. 查询多条数据:linkUrl.id 属于分级查询 public Lis

随机推荐