MySQL select, insert, update batch operation statement code examples

In projects, batch operation statements are often needed to operate data. When using batch statements, you need to pay attention to the conditional restrictions of the MySQL database. The main restriction here is the size of the data. Here is a brief summary of the basic operations of batch insertion, batch search, and batch update.

1. Batch insert statements. The following statements have been used to insert more than 10,000 data in a test environment. The specific number depends on the table structure and field content.

<insert id="addTextPushList" paramerterType="java.util.List">
  INSERT INTO SYS_TEXT_PUSH(
      PUSH_ID,
      USER_UM_ACCOUNT,
      USER_NAME,
      SECTION,
      USER_MOBILE,
      PUSH_STATUS,
      PROMOTE_ID,
      CREATED_BY,
      CREATION_DATE,
      ENABLED_FLAG  
)values
<foreach collection=”list” item = “item” separator=”,”>
  (
    #{item.pushId},
    #{item,userUmAccount},
    #{item.userName},
    #{item.section},
    #{item.userMobile},
    #{item.pushStatus},
    #{item.promoteId},
    #{item.createdBy},
    #{item.creationDate},
    #{item.enabledFlag}
)
</foreach>
</insert>

2. Batch select query statement

<select id = "getTextPromoteByIds" parameterType = "java.util.list" resultMap = "textPromoteMap">
  SELECT 
    *
  FROM SYS_TEXT_PROMOTE 
  WHERE
     TEXT_PUSH_FLAG = 1
    AND PROMOTE_ID IN
  <foreach collection=”list” item = “item” open=”(” separator=”,” close =”)”>
  #{item}
</foreach>
</select>

3. Batch update statements.

The first case is when the values to be updated are the same:

<update id="updatePushStatus" parameterType="java.util.List">
  update
     SYS_TEXT_PUSH
  SET
    PUSH_STATUS = 1,
    LAST_UPDATE_DATE = NOW()
  WHERE
      PUSH_ID IN
<foreach collection=”list” item = “item” open=”(” separator=”,” close =”)”>
  #{item}
</foreach>
</update>

The second case is when the values to be updated are different:

<update id="updatePushStatus" parameterType="java.util.List">
<foreach collection="list" item = "item" index = "index">
  update
     SYS_TEXT_PUSH
  SET
    PUSH_STATUS = #{item.pushStatus},
    LAST_UPDATE_DATE = NOW()
  WHERE
      PUSH_ID = #{item.pushId}
</foreach>
</update>

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:

A brief discussion on MySQL select optimization solution
MySQL select results to perform update example tutorial
Solve the problem that MySQL read-write separation causes data not to be selected after insert
How MySQL Select Statement is Executed
Implementation of MySQL select in subquery optimization
MySQL learning notes: complete select statement usage example detailed explanation
A brief understanding of MySQL SELECT execution order
Explanation of mysql transaction select for update and data consistency processing
The difference between Update and select in MySQL for single and multiple tables, and views and temporary tables
Detailed explanation of the use of MySQL select cache mechanism
Summary of Select usage in MySql database
How a select statement is executed in MySQL

<<: Detailed tutorial on uploading and configuring jdk and tomcat on linux

>>: Vue uses Baidu Maps to realize city positioning