how to affect each colmn in every table(mysql)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
g_alex_stef
Forum Newbie
Posts: 3
Joined: Thu Oct 19, 2006 10:14 am

how to affect each colmn in every table(mysql)

Post by g_alex_stef »

Hello people,
i am wondering if there is any script or class that can affect every column in all tables of a database
unfortunately my knoledge yet in mysql is pour so i cant write it by myself even though i imagine it might be too easy.
if someone knows any class or can write one for me i would apreciate it.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

what are you trying to do with this?
g_alex_stef
Forum Newbie
Posts: 3
Joined: Thu Oct 19, 2006 10:14 am

Post by g_alex_stef »

i need to change the collation in every single column and this is the only way i have figure out until now.
i will use the following queries:

Code: Select all

ALTER TABLE table MODIFY column BINARY(n); 
ALTER TABLE table MODIFY column CHAR(n) CHARACTER SET greek
but instead of table i must insert a variable which is generated in a loop i suppose the problem is i dont know mqsql at all....
i could that manually but my db is too big

thanks for showing interest :)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Actually you don't want it to affect every column in every table, do you? What would it do to integer or date columns?
g_alex_stef
Forum Newbie
Posts: 3
Joined: Thu Oct 19, 2006 10:14 am

Post by g_alex_stef »

well i suppose that an "if" statement would solve that problem, a fuction whith an if statement affecting only char(), varchar() and text, i have no tinytext etc types on my db
to be more prescise for example:

Code: Select all

function choosetype(curent_table,current_column,new_type,old_type) {
     if type of current table current_column is char() then 
            new_type=bin()
            old_type=char()
     end if
     if type of current table current_column is varchar() then 
            new_type=varbin() 
            old_type=varchar()
    end if
     if type of current table current_column is text then 
            new_type=Blob
            old_type=text
     end if
 }
    
loop tables{
    loop columns{
          function(table,column,newtype,oldtype)
               ALTER TABLE table MODIFY column newtype; 
               ALTER TABLE table MODIFY column oldtype CHARACTER SET greek
   }
}
***something very important is the length of every column ****
i hope you can understand what i am looking for
but i need that in php!!!!! :lol: i dont know php neither mysql...............
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Post Reply