Skip to content

Instantly share code, notes, and snippets.

@Rican7
Created November 26, 2012 20:38
Show Gist options
  • Save Rican7/4150467 to your computer and use it in GitHub Desktop.
Save Rican7/4150467 to your computer and use it in GitHub Desktop.
MySQL: Change all columns starting with "is_" of a table from "int"'s to "boolean"s
DROP PROCEDURE IF EXISTS changeIsColsToBool;
DELIMITER //
CREATE PROCEDURE changeIsColsToBool(
myschema VARCHAR(255),
defaultVal BOOL
)
BEGIN
DECLARE colname VARCHAR(255) default 'is\_%';
DECLARE mytable VARCHAR(255);
DECLARE col VARCHAR(255);
DECLARE dtype VARCHAR(255);
DECLARE mysql VARCHAR(255);
DECLARE done INT default false;
DECLARE allcols cursor for
select
c.TABLE_NAME,
c.COLUMN_NAME,
c.DATA_TYPE
from
INFORMATION_SCHEMA.COLUMNS c
inner join INFORMATION_SCHEMA.TABLES t on
c.TABLE_CATALOG = t.TABLE_CATALOG
and c.TABLE_SCHEMA = t.TABLE_SCHEMA
and c.TABLE_NAME = t.TABLE_NAME
where
c.DATA_TYPE like '%int%'
and c.column_name like colname
and c.TABLE_SCHEMA = myschema
;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
open allcols;
fetch_loop: LOOP
fetch next from allcols into mytable, col, dtype;
if done then
LEAVE fetch_loop;
end if;
set @mysql = CONCAT('alter table ', myschema, '.', mytable, ' CHANGE `', col, '` `', col, '` BOOLEAN NULL');
if defaultVal then
set @mysql = CONCAT(@mysql, ' DEFAULT TRUE');
elseif defaultVal = false then
set @mysql = CONCAT(@mysql, ' DEFAULT FALSE');
else
set @mysql = CONCAT(@mysql, ' DEFAULT NULL');
end if;
PREPARE statement from @mysql;
SET @myschema = myschema;
SET @mytable = mytable;
SET @col = col;
execute statement;
DEALLOCATE PREPARE statement;
end LOOP;
close allcols;
SELECT * FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = myschema AND `COLUMN_NAME` LIKE colname;
END
//
DELIMITER ;
call changeIsColsToBool( DATABASE(), null );
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment