Cacomania: tag

Cacomania

Remove duplicate values from a mysql table

Guido Krömer - 1278 days ago - Tags:

I needed a way to clean up a MySQL table containing many duplicate rows because of a missing UNIQUE KEY.

One solution would be a script which searches and removes those dups, but depending on the number of rows this would be a heavy lifting job. A better solution is performing this task completely in the database without moving the data into an application and the sanitized data back to the database.

You do not need any PL/SQL…, five simple SQL statements can handle the task. A new table with the structure of the table containing the dubs, with a unique index preventing the dups, has to be created. The rows from the old table has to be inserted into the new one, by using the IGNORE keyword the query will not stop when reaching a duplicate entry, those dups gets just ignored. The last step is replacing the new table with the old one.

CREATE TABLE `table_without_dups` LIKE `my_table`;

ALTER TABLE `table_without_dups`
ADD UNIQUE `my_unique_key` (`col_1`, `col_n`);

INSERT IGNORE INTO `table_without_dups`
SELECT * FROM `my_table`;

DROP TABLE `my_table`;
RENAME TABLE `table_without_dups` TO `my_table`;

I hope my small posting helped you, feel free to leave a comment.

Comment [3]

RESTful API with Slim PHP and SQLite

Guido Krömer - 1458 days ago - Tags: , , ,

I needed a small RESTful API, primarily for playing around with AngularJS. The Slim Framework perfectly fits my needs. It is a PHP micro framework for writing a prototype or really small applications, without the overhead and the steep learning curve of a complete framework likes Yii, Symfony or Zend. At the same time, you get stuck at the Zend2 module configuration, you could have finished a working prototype with Slim.

I used SQLite as database for the API, because SQLite is configuration free it is perfect for prototyping. For keeping the focus, of this tutorial, at the Slim Framework itself, I skipped using any kind of ORM framework. Due the use of PDO any other SQL Database like MySQL could be used, too.

To gain some practical benefit from this tutorial the REST API going to be discussed in this tutorial manages bookmarks in a CRUD style. I hope this help me with my bookmark chaos among Linux, Windows, tablet and smart phone.

read more TEST

Comment [20]

MySQL rename a database with bash

Guido Krömer - 1541 days ago - Tags: ,

Renaming an MySQL table is straightforward with RENAME TABLE, but renaming a whole database with RENAME DATABASE has been removed in MySQL 5.1.23 because it could be dangerous.

Since I needed this functionality some lines of bash does nearly the same by creating a new database and renaming each table like this: RENAME TABLE old_database.my_table new_database.my_table.

#!/bin/bash
execute_sql () {
  echo "$1" | mysql
}

rename_database () {
  FROM=$1
  TO=$2

  execute_sql "CREATE DATABASE IF NOT EXISTS $TO"

  TABLES=`execute_sql "SHOW TABLES FROM $FROM"`

  for TABLE in $TABLES; do
    execute_sql "RENAME TABLE $FROM.$TABLE TO $TO.$TABLE"
  done;

  echo "Done, drop the old database ($FROM) if needed."
}

rename_database $1 $2

Comment