Batch add fields for mysql tables and mysql tables

Batch add fields for mysql tables and mysql tables

This similar table Shard is used in the project. What should I do if I want to add a field?

　　

 

Dba said that it would be enough to get a script for batch processing. In a slot, where would it be? So I kept asking the dba, And the dba gave a script.

　　

1 #! /Bin/bash 2 3 for db in {warn, rmlog_bs_db_02, rmlog_bs_db_03, rmlog_bs_db_04, clerk, rmlog_bs_db_10, clerk, clerk 4, clerk, rmlog_bs_db_15, rmlog_bs_db_16} 5 do 6 7 all_table = '/mysql/product/bin/mysql-uroot-pxxxxx-e "select table_name from information_schema.tables where table_schema =' $ {db} 'and table_name like 'Log _ imap_mail_2016 % '"'8 9 for I in $ all_table10 do11 12/mysql/product/bin/mysql-uroot-pxxxx-e" alter table $ db. $ I add xxxx "13 done14 doneView Code

A shell script is used to create tables in batches instead of table shards.

I think this is a shell script and cannot be tested on my local machine, so I want to be unable to create an SQL script. At first, I thought it was just like oracle, but I was not very familiar with mysql and did not write this stuff, so I went online for a long time.

　　

drop procedure if exists useCursor;  CREATE PROCEDURE useCursor()    BEGIN      DECLARE oneAddr varchar(8) default '';      DECLARE allAddr varchar(40) default '';      DECLARE done INT DEFAULT 0;      DECLARE curl CURSOR FOR select table_name from information_schema.tables where table_schema='testdb' and table_name like  'user%';      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;      OPEN curl;      REPEAT        FETCH curl INTO oneAddr;        IF not done THEN           set @sql=concat('alter table ',oneAddr,' add comma varchar(10)');         PREPARE stmt from @sql;          execute stmt;      END IF;      UNTIL done END REPEAT;      select allAddr;    CLOSE curl;       END;  call useCursor(); 

You just need to make such a thing.

Declare continue handler for not found set done = 1; this statement indicates that if select has no result, set done to 0. I tried to use question marks for parameter binding, but I couldn't. Maybe the mysql version on my local machine is too low. You have to splice the SQL statement.


For the mysql midstream mark loop, you can also write it together. One is repeat, the other is while, and the other is loop. In addition, the execution body does not need to write begin end, so you can directly write the statement.