Tag Archives: MySQL
How to delete records with duplicate fields, such as dupplicate emails?
To delete records where only a field is duplicated, we can use a similar technique to the general duplicate removal technique: CREATE TABLE temp AS SELECT DISTINCT ON (email) * FROM table_to_deduplicate; DROP TABLE table_to_deduplicate; ALTER TABLE temp RENAME TO … Continue reading
How to delete dupplicate records in DB2, Oracle, MySQL, and PostgreSQL
To delete dupplicate records in SQL, the following sequence of commands will do the trick: CREATE TABLE temp AS SELECT DISTINCT * FROM table_to_deduplicate; DROP TABLE table_to_deduplicate; ALTER TABLE temp RENAME TO table_to_deduplicate; Be mindful of the fact that this … Continue reading
Which MySQL database engine to pick for a given table?
MySQL allows to select a different king of engine on a per table basis at creation time of each table. Each engine has its advantages and caveats. Here is a brief summary: MyISAM: fastest disk based, least space requirement, non-transactional, … Continue reading
MySQL default values on INSERT
When inserting into regular database systems, like Oracle, DB2, and even PostgreSQL, the omitted attributes from an INSERT are automatically set to default. Not so with MyISAM tables on MySQL. The syntax required by MySQL for an implicit default when … Continue reading
Increase maximum table space in MySQL
For those of us that are still forced to used 32bit MySQL, there is a table size limit of 4GB by default (even though the file size limit on those systems is 4TB on ext3 – 2TB on NTFS). Here … Continue reading
Export MySQL table as csv file
Exporting from an RDBMS always depends on the syntax of the specific RDBMS in question. For MySQL, here is how to transform a SELECT statement into a CSV export: SELECT a,b INTO OUTFILE ‘/tmp/result.csv’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED … Continue reading
Drop Index in MySQL takes forever
Even though for our core systems we have migrated most databases to DB2, we still have to deal with MySQL for some side systems, such as CRM, Blog, etc. One of the MySQL features that I recently noticed is … Continue reading