In MySQL, format_pico_time() is a Performance Schema function that converts a numeric Performance Schema latency or wait time in picoseconds into a human-readable format. It returns a string consisting of a value and a units indicator.
Author: Ian
How to Select Rows Where a Column is NULL in SQL
When learning SQL, a common mistake when checking for NULL values is to use an equality operator. In SQL, a NULL value is treated a bit differently to other values.
In SQL we use IS NULL instead of = NULL. Likewise, we use IS NOT NULL instead of <> NULL or != NULL to select those columns that don’t have a NULL value.
MySQL DEFAULT() Function Explained
In MySQL, the DEFAULT() function returns the default value for a given column in the database.
If the column doesn’t have a default value, an error is returned.
Continue reading3 Ways to Get the Data Directory in MySQL
In MySQL, the data directory stores information managed by the MySQL server. Each subdirectory of the data directory is a database directory and corresponds to a database managed by the server.
If you ever need to find out where the data directory is located on your MySQL implementation, below are some options to try.
Continue reading6 Ways to Fix Error 1055 “Expression … of SELECT list is not in GROUP BY clause and contains nonaggregated column…” in MySQL
If you’ve been using MySQL for any decent amount of time, it’s likely you’ll be familiar with error 1055 that reads something like “Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column…“, where #N is the expression number of an expression/column in your SELECT list.
This error can occur when we include a column in the SELECT list, but we omit it from the GROUP BY clause.
There are several ways we can go about resolving this issue. Below are six options for dealing with this issue.
Continue readingHow AUTO_INCREMENT Works in MySQL
In MySQL, we can include the AUTO_INCREMENT attribute within a column definition in order to create an auto-incrementing column.
Generally, when we do this MySQL will automatically generate a value for us whenever we insert a new row into the table. I say “generally” because we can still explicitly insert our own value if that’s required.
Continue reading4 Ways to Fix Error 1048 “Column ‘…’ cannot be null” in MySQL
If you’re getting error 1048 that reads something like “Column ‘ColumnName’ cannot be null” (where ColumnName is the name of a column you’re trying to insert data into), then it’s probably because you’re trying to insert a NULL value into that column, but the column has a NOT NULL constraint (which prevents NULL values from being inserted).
We have a few options when it comes to fixing this issue. The most obvious is to ensure we provide a non-NULL value for the column. Alternatively, if the column should be able to accept NULL values, then we can remove the NOT NULL constraint from the column. Another option is to use the IGNORE keyword to ignore the error. And another way to deal with the error is to disable strict mode.
MySQL Aggregate Functions (Full List)
The following table contains a full list of the aggregate functions in MySQL.
Continue readingMySQL IGNORE Clause Explained
In MySQL, we can use the IGNORE clause in statements that change data in order to ignore certain errors that might occur had we not used it. When IGNORE is used, such errors are downgraded to warnings.
For example, we can use IGNORE in an INSERT statement to ignore any errors we might normally get if we tried to insert a NULL value into a NOT NULL column. In such a case, MySQL won’t return an error. Instead, it will deal with the issue in another way, and provide us with a warning.
If we have strict mode enabled, we can use IGNORE to force MySQL to act as though strict mode is disabled. However, IGNORE can also be used to downgrade certain errors regardless of the strict mode setting.
How to Disable Strict Mode in MySQL
Strict mode controls how MySQL handles invalid or missing values in statements that change data, such as in INSERT and UPDATE statements.
Strict mode (aka strict SQL mode) is in effect if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled.
In MySQL 8.0, the default SQL mode includes STRICT_TRANS_TABLES, which enables strict SQL mode for transactional storage engines, and when possible for nontransactional storage engines.
We can disable strict mode in our session by removing it from our @@sql_mode variable. We can also disable strict mode at server startup, so that we don’t have to do it at runtime.