MySQL Functionalism

Just another Digital Functionalism Site

Find Records Containing Percentage Sign

The “\%” and “\_” sequences are used to search for literal instances of “%” and “_” in pattern-matching contexts where they would otherwise be interpreted as wildcard characters. If you use “\%” or “\_” outside of pattern-matching contexts, they evaluate to the strings “\%” and “\_”, not to “%” and “_”.

So if you want to find records that have a percentage sign inside them, using pattern-matching, you would escape the percentage sign (%) as follows:

SELECT *
FROM `table`
WHERE `columne' LIKE '%\%%'

whereas if you wanted to search for a field that was equal to a percentage sign, then escaping is not required:

SELECT *
FROM `table`
WHERE `column' = '%'

MySQL Find Duplicate Records

If you are inserting the data into the table, you could either check for dupes before inserting, or make a unique key on the fields that comprise a unique record for your application, which will prevent duplicates from entering the table in the first place. This example is for cases where the data is already in the table and you want to see if there are duplicates, where a duplicate is defined by any number of arbitrary fields having the same value. Following is a general example to illustrate the point.

SELECT COUNT( CONCAT( `field1` , `field2` , `field3` ) ) AS `count` , 
CONCAT( `field1` , `field2` , `field3` ) AS `record`
FROM `table`
GROUP BY CONCAT( `field1` , `field2` , `field3` )
ORDER BY `count` DESC

A real world application might be to search a third-party mailing list to identify duplicate records where the name and address are the same. In this case we could use something like the following:

SELECT COUNT( CONCAT( `firstname` , `lastname` , `address` , `city` , `state` ) ) AS `count` , 
CONCAT( `firstname` , `lastname` , `address` , `city` , `state`) AS `record`
FROM `table`
GROUP BY CONCAT( `firstname` , `lastname` , `address` , `city` , `state` )
ORDER BY `count` DESC

MySQL Find and Replace

This is a good “oh shit” tool… It is a simple way to do a search and replace across all values in a MySQL database column. It will replace every occurrence in the column so use with caution…

UPDATE `table` SET `column` = REPLACE(`column`, 'find', 'replace')

I use this often to update WordPress when I am moving a site from staging to a live server. In the following example I update the wp_posts table to replace the staging url with the live url.

UPDATE `wp_posts` SET `post_content` = REPLACE(`post_content`, 'staging.bradino.com', 'www.bradino.com');
UPDATE `wp_posts` SET `guid` = REPLACE(`guid`, 'staging.bradino.com', 'www.bradino.com');

MySQL Select WHERE IN

Here is a basic MySQL query to find beers that are .

SELECT * FROM `beers` WHERE `style` = 'ale' OR `style` = 'porter' OR `style` = 'stout'

Here is the same request, more efficiently constructed using IN

SELECT * FROM `beers` WHERE `style` IN ('ale','porter','stout')

If you are like me and dynamically create SQL statements using PHP then you get the idea of how easy it would be to create this statement. Let’s say you have an array of possible values.

$styles = array('ale','porter','stout');
 
$sql = "SELECT * FROM `beers` WHERE `style` IN  ('" . implode("','", $styles) . "')" ;

MySQL TEXT Field Size Limits

Today while screen scraping pages, and storing the results in a MySQL database table for later parsing, I discovered that MySQL TEXT fields have a limit of 65,536 bytes (2^16 bytes). I did not receive an error when inserting the record so I was not immediately aware of the problem.

It turns out that MySQL will silently truncate your data when you insert anything larger than the allowed 65k bytes in a TEXT field. After some digging on mysql.com I learned that the MEDIUMTEXT field will store 16,777,216 bytes (2^24 bytes) and the LONGTEXT field can handle 4,294,967,296 bytes (2^32 bytes).

© 2016 MySQL Functionalism

Theme by Anders NorenUp ↑