MySQL batch adding and storing method examples

When logging in to the stress test, many different users are required, and new data needs to be added to the database

#Batch add user accounts - stored procedure:
delimiter //
drop procedure if exists test;
create procedure test()
 
begin
DECLARE i int;
set i = 1;
while i<21 do
insert into hg_user values ​​(concat("OM_TEST",cast(i as CHAR)),concat("OM_TEST",cast(i as CHAR)),"F1B2F5B9FBC8B513",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
set i = i+1;
end while;
select * from test;
end//
call test();

delimiter is the MySQL delimiter. The default delimiter in the MySQL client is a semicolon (;).

If you input more statements at one time and there is a semicolon in the middle of the statement, you need to specify a special separator, commonly used ones are // and &&.

The above is, first set the separator to //,

The statement is not executed as a whole until the next // is encountered.

After execution, the last line, delimiter; resets the MySQL delimiter to a semicolon.

If not modified, all separators in this session will be based on //.

concat is a character concatenation, which connects multiple strings into one string.

Syntax: concat(str1, str2,...)

eg:select concat (id, name, score) as info from tt2; 1Xiao Ming 60

The cast function is used to explicitly convert an expression of one data type to another data type.

Syntax: CAST (expression AS data_type)

There are restrictions on the types that can be converted. The type can be one of the following values:

• Binary, with the effect of binary prefix: BINARY
• Character type, with parameters: CHAR()
• Date: DATE
• Time: TIME
• Date and time type: DATETIME
• Floating point numbers: DECIMAL
• Integer: SIGNED
• Unsigned integer: UNSIGNED
  

Batch deletion solution (the same applies to deleting users)

#Delete solution - stored procedure;
delimiter //
drop procedure if exists test;
create procedure test()
 
begin
DECLARE i int;
set i = 1;
while i<11 do
DELETE from hg_application_flow_template where user_name=concat("OM_TEST",cast(i as CHAR));
DELETE from hg_application_flow_template_details where created_by=concat("OM_TEST",cast(i as CHAR));
set i = i+1;
end while;
select * from test;
end//
call test();

Summarize

This is the end of this article about MySQL batch addition and storage. For more relevant MySQL batch addition and storage content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!