Helper snippets

mysql-helper.sql

# Remove row duplicates
SELECT DISTINCTROW * INTO doublettesTable FROM uniqueTable;
 
# Count column duplicates
SELECT col, count(*) AS cnt 
FROM table 
GROUP BY col 
HAVING cnt > 1;
 
# Count duplicates with two cols
SELECT col1, col2, count(*) AS cnt 
FROM table 
GROUP BY col1, col2 
HAVING cnt > 1;
 
# Replace
UPDATE table SET col = REPLACE(col, 'search', 'replace');
 
# Check dataintegration (1:n)
SELECT id FROM tableN WHERE id NOT IN (SELECT id FROM tableN);

# Check dataintegration (n:m)
SELECT idN, idM FROM relationTable 
WHERE idN NOT IN (SELECT idN FROM tableN)
   OR idM NOT IN (SELECT idM FROM tableM);
 
# nl2br
UPDATE table SET col = REPLACE(col, char(10), '[LF]');
UPDATE table SET col = REPLACE(col, char(13), '[CR]');
UPDATE table SET col = REPLACE(col, '[CR][LF]', '<br />');
 
# Set connection to utf8
SET NAMES 'utf8';

# Substring
SELECT SUBSTRING(col, 1, 10) FROM table;

# String shorten
SELECT IF(CHAR_LENGTH(col)>30),
          CONCAT(LEFT(col, 20), ' ... ', RIGHT(col, 5)),
          title
FROM table;

# Convert charset
SELECT CONVERT(col USING utf8) FROM table;

# Cast operator
INSERT INTO table (col) VALUES (_utf8 'anystring');

# Variables
SET @varname = 3; # setter: operator '='
SELECT @varname := COUNT(*) FROM table; # setter in select statement: operator ':=' 
SELECT @varname; # getter