We’ll be using the MySQL built-in function char_length to demonstrate how to filter and sort based on a size of a columns width in characters.
Using Create/Select to Create a New Table From Another
In a single statement you can copy the structure and data from one table in MySQL and populate the results of a query into the
String Concatenation Using concat Function
Unlike SQL Server, String concatenation works using a function. It’s a simple function that you pass comma separated values into in the order you want
Query using Avg Ignoring Certain Values
In this case we want to get the average of values but we also want to ignore certain values, say if when the deviceType column
Select Last N Characters of a Field
Using the substring function, this example below will select the leftmost 6 characters from a string field named “field_name” in the table tbl_stuff with mysql.
Seeing Full Warnings That Just Happened After a Query
After you run a query on MySQL server you may get warning messages. To see the complete warnings you simply run the command show warnings.
coalesce Function Handy for Outer Joins and Nulls
The built-in function coalesce in MySQL is great for outer joins where the return or expected answer can be null. Coalesce can be used anytime
Ignoring Insert Errors
In the MySQL sql language, the insert modifier ignore can sometimes come in handy if you’re wanting to run the query but not worry about
Parsing Date/Time From String
This below would parse the string, in the specified format into a valid date time object. STR_TO_DATE only works when the date mask is valid
How to Backup and Restore with mysqldump
Backing up and restoring a MySQL or MariaDB database is super easy. I’ve listed out a few different ways you can do it. Example MariaDB/MySQL