Skip to content

Instantly share code, notes, and snippets.

@ssippe
Created June 4, 2020 00:40
Show Gist options
  • Save ssippe/e1f722d0af244033335133a7621ba0a9 to your computer and use it in GitHub Desktop.
Save ssippe/e1f722d0af244033335133a7621ba0a9 to your computer and use it in GitHub Desktop.
mysql column lists for inserts updates etc.
set @schema = 'base_sms';
set @table = 'bayprofiles';
-- column name list
select concat('`',COLUMN_NAME,'`,') as columnName from information_schema.COLUMNS where TABLE_SCHEMA=@schema and TABLE_NAME=@table;
/*
`id`,
`profileId`,
`bayWidth`,
`bcolumn`,
*/
-- table param list
select concat('@',COLUMN_NAME,',') as paramName from information_schema.COLUMNS where TABLE_SCHEMA=@schema and TABLE_NAME=@table;
/*
@id,
@profileId,
@bayWidth,
@bcolumn,
...
*/
-- update list
select concat('`',COLUMN_NAME, '` = @',COLUMN_NAME,',') as updateStmt from information_schema.COLUMNS where TABLE_SCHEMA=@schema and TABLE_NAME=@table;
/*
`id` = @id,
`profileId` = @profileId,
`bayWidth` = @bayWidth,
`bcolumn` = @bcolumn,
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment