Never been to CodeSnippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world (or not, you can keep them private!)

About this user

Jamie Wilkinson http://tramchase.com

3 total

On This Page:

  1. 1 Bulk convert InnoDB tables to MyISAM
  2. 2 Delete duplicate rows from a table
  3. 3 vi find/replaces for adding a table prefix to a SQL dump

Bulk convert InnoDB tables to MyISAM

to mysql sql innodb myisam convert by jamiew on Apr 02, 2008 1 Comments
// produces some handy ALTER statements
// I think there's a way to this w/ mysqladmin but I couldn't find it.

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=MyISAM;') FROM information_schema.tables WHERE engine = 'InnoDB';

Delete duplicate rows from a table

to mysql sql database db by jamiew on Nov 06, 2007
// Whoops, didja forgot to add a unique index to that column? No problem

CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];
DROP TABLE old_table;
RENAME TABLE new_table TO old_table;

vi find/replaces for adding a table prefix to a SQL dump

to regex sql vi vim findreplace subtitution by jamiew on Jun 11, 2007
Was importing a DB but wanted to prefix the tables, but the file was too large for TextMate. Here's all the vim substitutions

%s/DROP TABLE IF EXISTS `/DROP TABLE IF EXISTS `prefix_/g
%s/CREATE TABLE `/CREATE TABLE `prefix_/g
%s/INSERT INTO `/INSERT INTO `prefix_/g
%s/LOCK TABLES `/LOCK TABLES `prefix_/g
%s/ALTER TABLE `/ALTER TABLE `prefix_/g
3 total

On This Page:

  1. 1 Bulk convert InnoDB tables to MyISAM
  2. 2 Delete duplicate rows from a table
  3. 3 vi find/replaces for adding a table prefix to a SQL dump