7 articles MySQL

MySQL DATE_FORMAT: Grouping Date & Time value fields into single days made easy

Posted on by Aaron Belchamber
To conform a datetime field to just a date in your results and make it easy to group TIMESTAMP fields in the format "2015-12-25 12:23:23" so you can group any datetime value into actual days for daily reports, use "DATE_FORMAT" below: In other words, if you have values from a SELECT statement that are…

Planning to design or redesign an organization’s database? Get back to basics and refer to MySQL’s reserved keyword list

Posted on by Aaron Belchamber
Oracle's MySQL is a staple in the open source web environment world.  Being familiar with the reserved words the database uses is just one way to make sure you don't have to refactor code or special-escape field names in queries later with the menacing back tick: ` Here is the link to MySQL's reserved keyword's…

MySQL: Using sub-selects to filter results of one table that is not contained in another table

Posted on by Aaron Belchamber
In MySQL, if you have two tables with a shared index and you would like to show the results of one table where the index is not within another table, you can use "NOT IN". Try Googling "MySQL NOT IN" and you get everything but this command, it's almost as if they need to…

MySQL: Dealing with time and date/time database values set inside fields that are defined as text, strings, or mediumtext

Posted on by Aaron Belchamber
It's happened to all of us at some point. You take over a project and some knucklehead who designed the database was too lazy to optimize it. This usually means you will end up with fields named "DateBought" and though it's supposed to always hold a date and/or datetime value, it's defined as some VARCHAR…

Changing MySQL “INSERT INTO” into “INSERT IGNORE INTO” can solve a lot of headaches when batch importing a lot of records

Posted on by Aaron Belchamber
Inserting a batch of records into MySQL will stop on the first instance the record already exists with a matching PRIMARY KEY. If you're at risk of this happening, open the .sql file and use your text editor to search for "INSERT INTO" and replace it with "INSERT IGNORE INTO" and this will skip any…