MySQL table snippets and fixes to common problems

MySQL table snippets and fixes to common problems

MySQL table snippets? Yes, there are quite a few of them in this article. But before we get to that, what is MySQL? Let’s start with the MySQL definition. MySQL is an Oracle-backed open source relational database management system (RDBMS) based on Structured Query Language (SQL).

Applications that use the MySQL database include TYPO3, MODx, Joomla, WordPress, Simple Machines Forum, phpBB, MyBB, and Drupal. MySQL is also used in many high-profile, large-scale websites, including Google (though not for searches), Facebook, Twitter, Flickr, and YouTube.

And after all that intro, you realize that MySQL is bigger than you initially thought. Unless you’ve been around for a while in the industry. If you are a newcomer, however, and you want to learn more about MySQL, the easiest way to do it is with MySQL table snippets.

MySQL table 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;

MySQL Find and Replace

UPDATE files SET filepath = REPLACE(filepath,'path/to/search','path/to/replace');

Selecting a Random Row In MySQL

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Create CSV from MySQL

SELECT a,b,c FROM my_table INTO OUTFILE '/ca.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Find duplicate records

SELECT id, COUNT(*) as n
FROM my_table
GROUP BY id
HAVING n >1;

Correcting wrong character encoding in MySQL data

UPDATE table SET column=CONVERT(CONVERT(CONVERT(column USING latin1) USING binary) using utf8);

Load CSV back into mySQL

LOAD DATA local INFILE 'unique.csv' INTO TABLE tablename
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(col1, col2, col3)

Drupal 6.x Find PHP code in database

select nid, vid from node_revisions where format in (select format from filters where module = 'php' and delta = 0);
select cid, nid from comments where format in (select format from filters where module = 'php' and delta = 0);
select bid, info from boxes where format in (select format from filters where module = 'php' and delta = 0);

Generate random string in MySQL

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 6) AS randomstring

Calculate Age from Date Of Birth

SELECT FLOOR(DATEDIFF(CURRENT_DATE(), dob) / 365);

Find & Replace

UPDATE table_name SET column_name = REPLACE(column_name, 'http:\/\/oldcontent.com', 'http:\/\/newcontent.com');

Select between two dates

SELECT * FROM files LEFT JOIN (dates) ON files.uploaded_id = dates.id WHERE date BETWEEN '2009-01-01' AND '2009-12-31'

Backup MySQL Database to SQL File with mysqldump

mysqldump --user=username --password=password --opt --add-drop-database --databases databasename > ~/path/to/file.sql

how to calculate last 7 days

WHERE mydatefld >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)

mysql dump

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

Mysql replace

# Replace string in all records from a table
UPDATE table_name SET column_name = REPLACE( column_name, '/dev', '' );
# Display a modified record set
SELECT REPLACE( column_name, '/dev', '' ) FROM table_name;

Convert timezone to local

SELECT CONVERT_TZ(mydatefield,'US/Pacific','CET') FROM mytable;

Search and Replace on Multiple Rows in SQL

UPDATE wp_posts SET post_content = REPLACE(post_content, 'staging.server.com', 'www.productionserver.com');

How to Save results from MySQL into an external file

echo "SELECT columns FROM table_name WHERE whatever='something'" | /path/to/mysql -uUSERNAME -pPASSWORD DATABASENAME > /tmp/outfile.txt;

Insert or update if unique key already exist

INSERT INTO `tableName`
VALUES (?,?,?,'-1',?,'0')
ON DUPLICATE KEY
UPDATE `id` = ?, `foo` = ?, `bar` = ?

mySQL – database backup commands

-- dump mySQL database to file:
VAR=$(date +�%y-%m-%d�); mysqldump -v -uUSERNAME -pPASSWORD -hlocalhost DATABASENAME > /BACKUPDIR/$VAR-DATABASENAME.sql ;

-- dump mySQL database to file and then move it:
VAR=$(date +�%y-%m-%d�); mysqldump -v -uUSERNAME -pPASSWORD -hlocalhost DATABASENAME > /BACKUPDIR/$VAR-DATABASENAME.sql ; mv /BACKUPDIR/DATABASENAME.sql /OTHERBACKUPDIR/$VAR-DATABASENAME.sql ;

MySql import

mysql -u [username] -p [dbname] < [filename].sql

find duplicate records

select address, count(address) as cnt
from mailing_list
group by address
having cnt > 1
order by cnt;

MySQL Find and Replace

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'find this string', 'replace found string with this string');

Change Drupal file directory path

UPDATE FILES SET filepath = REPLACE(filepath, 'sites/default/files/old/','sites/default/files/new/');

Find all tables in MySQL database containing specific column names

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('columnA','ColumnB')
AND TABLE_SCHEMA='YourDatabase';

Find values that occur exactly once in table

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

MySql script to find invalid email addresses

SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%'

MySQL Age Calculation

DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(DOB, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(DOB, '00-%m-%d')) >= 65

Left Joins to link three or more tables

select * from (bdg left join res on bdg.bid = res.bid) left join dom on res.rid = dom.rid where dom.rid is NULL;

MySQL REGEX match comma-delimitted list

WHERE id REGEXP '(,|^){$this->id}(,|$)'

Get random record based on weight

SELECT * FROM Table ORDER BY Rand( ) * ( 1 / Weight

We hope that these MySQL table snippets are the thing you were looking for. We will update this article with other MySQL snippets that we come across in the future, so make sure to bookmark it.

If you enjoyed reading this article about MySQL table snippets, you should also read these:


Warning: Use of undefined constant php - assumed 'php' (this will throw an Error in a future version of PHP) in /var/www/wpdatatables.com/wp-content/themes/tabor/components/post/content.php on line 60
Up Next:

CSS tables and their code that you can use

CSS tables and their code that you can use