Skip to content

Instantly share code, notes, and snippets.

@igniteflow
Created May 9, 2011 09:10
Show Gist options
  • Save igniteflow/962265 to your computer and use it in GitHub Desktop.
Save igniteflow/962265 to your computer and use it in GitHub Desktop.
MySQL Snippets
/* match a string at the beginning of string */
SELECT 'Test' REGEXP '^The'; -- 0
SELECT 'The Test' REGEXP '^The'; -- 1
/* if a name is not prefixed with 'The ' then add it */
UPDATE [table]
SET Name = CONCAT('The ', TRIM(Name))
WHERE Name NOT REGEXP '^The'
/* copy a column from one table to another */
INSERT INTO [table] ([column]) SELECT [column] FROM [table]
/* remove all whitespace */
UPDATE [table] SET [column] = REPLACE([column], ' ', '')
/* (this is BASH) drop all tables in a database */
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
/* change to collation of a table and all of its existing columns */
alter table [table] convert to character set utf8 collate utf8_general_ci;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment