· 8 years ago · Jan 04, 2018, 09:58 AM
1alter table datatable_??
2add column ID int auto_increment not null first,
3add primary key (ID);
4
5SELECT CONCAT('ALTER TABLE ',
6table_schema,
7'.',
8table_name,
9' ADD COLUMN id INT AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;') AS ddl
10INTO OUTFILE '/tmp/alter_table.sql'
11FROM information_schema.tables
12WHERE table_schema = 'db_name'
13AND table_type = 'base table';
14
15. /tmp/alter_table.sql
16
17select concat('alter table ',table_name,' add column ID int auto_increment not null primary key first;')
18from information_schema.tables
19where table_schema = 'db_name' and table_type = 'base table';
20
21DELIMITER $$
22
23USE `db_name`$$
24
25DROP PROCEDURE IF EXISTS `alter_test_1`$$
26
27CREATE DEFINER=`db_name`@`10.%` PROCEDURE `alter_test_1`()
28BEGIN
29
30DECLARE v_finished INTEGER DEFAULT 0;
31DECLARE v_table VARCHAR(100) DEFAULT "";
32DECLARE stmt VARCHAR(500) DEFAULT "";
33
34DECLARE column_cursor CURSOR FOR
35SELECT * FROM `information_schema`.`tables` WHERE table_schema = 'db_name' AND table_name LIKE 'mytables_%';
36
37DECLARE CONTINUE HANDLER
38FOR NOT FOUND SET v_finished = 1;
39
40OPEN column_cursor;
41
42alter_tables: LOOP
43
44FETCH column_cursor INTO v_table;
45IF v_finished = 1 THEN
46LEAVE alter_tables;
47END IF;
48
49SET @prepstmt = CONCAT('ALTER TABLE adtracker','.',v_table,' ADD COLUMN id INT AUTO_INCREMENT NOT NULL;');
50
51PREPARE stmt FROM @prepstmt;
52EXECUTE stmt;
53DEALLOCATE PREPARE stmt;
54
55END LOOP alter_tables;
56
57CLOSE column_cursor;
58
59END$$
60
61DELIMITER ;
62
63<?php
64 // ====Variable ============
65 $sServerHost="localhost";
66 $sLocalHost="localhost";
67 $sServerUser="root";
68 $sLocalUser="root";
69 $sServerPassword="";
70 $sLocalPassword="";
71 $sLocalDatabaseName="test";
72 $sServerDatabaseName="test";
73 $Add_columnname="session_language";
74 // ====db connection============
75 $conn = @mysql_connect($sServerUser,$sServerUser,$sServerPassword);
76 if(!$conn) {
77 $conn = mysql_connect($sLocalHost,$sLocalUser,$sLocalPassword);
78 }
79 $db=mysql_select_db($sServerDatabaseName);
80 if(!$db){
81 $db=mysql_select_db($sLocalDatabaseName);
82 }
83 // =====basic function liberary============
84 class Model
85 {
86 function find_query_all($pred = '', $params = array(), $select = '*')
87 {
88 global $db;
89 $sql = $pred;
90 $rows = array();
91 $rows =$this->getAll($sql, $params);
92 $this->_check_error($rows);
93 if(count($rows)>0){
94 $this->_row_count=count($rows);
95 }
96
97 else
98 $this->_row_count=0;
99 return $rows;
100 }
101
102 //====== fetch rows from db and return array of row object
103 function getAll($sqlQuery='',$params= array())
104 {
105 $msg="";
106 $this->_sqlQuery="";
107 $sQuery=$this->MakeSQLQuery($sqlQuery,$params);
108 $this->_sqlQuery=$sQuery;
109 $res=mysql_query($sQuery) or die(mysql_error());
110 $results = array();
111 if(mysql_num_rows($res)>0){
112 while ($row = mysql_fetch_object($res)) {
113 $results[] = $row;
114 }
115 }
116 return $results;
117
118 }
119
120 function MakeSQLQuery($str='',$params= array())
121 {
122 foreach($params as $k=>$v){
123 $params[$k]=$this->_quote($params[$k]);
124 }
125 $str=$this->replace_different("?",$params,$str);
126 return $str;
127 }
128
129 function replace_different($search,$replace,$string) {
130 $occs = substr_count($string,$search);
131 $last = 0;
132 $cur = 0;
133 $data = '';
134 for ($i=0;$i<$occs;$i++) {
135 $find = strpos($string,$search,$last);
136 $data .= substr($string,$last,$find-$last).$replace[$cur];
137 $last = $find+strlen($search);
138 if (++$cur == count($replace)) {
139 $cur = 0;
140 }
141 }
142 return $data.substr($string,$last);
143 }
144
145 // check for a database error
146 function _check_error($obj, $msg = 'Database Error')
147 {
148
149 if (!$this->isError($obj))
150 {
151 $this->raise_error($msg . ': ' .$obj->getMessage(). mysql_error());
152 }
153 return ;
154 }
155
156 function isError($value)
157 {
158 if(isset($value))
159 return true;
160 else
161 return false;
162 }
163
164 }
165 // main operation from here
166 //====define object of class========
167 $tbl_col = new Model;
168 $sqlquery_col="SELECT table_name FROM information_schema.tables where table_schema='".$sLocalDatabaseName."'";
169 $res_tab_col=$tbl_col->find_query_all($sqlquery_col);
170 foreach($res_tab_col as $r=>$v){
171 $res=mysql_query("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '".$sLocalDatabaseName."' AND TABLE_NAME ='".$v->table_name."' and COLUMN_NAME!='".$Add_columnname."' ORDER BY ORDINAL_POSITION DESC LIMIT 1");
172 if(mysql_num_rows($res)>0){
173 while ($row = mysql_fetch_object($res)) {
174 $last_column= $row->COLUMN_NAME;
175 $alter_query="ALTER TABLE `".$v->table_name."` ADD `".$Add_columnname."` INT NOT NULL DEFAULT '1' AFTER `".$last_column."` ";
176 mysql_query($alter_query);
177 }
178 }
179 }
180 ?>