Category: MySQL

Understanding the PS_THREAD_ID() Function in MySQL

Posted on by Ian

In MySQL, we can use the PS_THREAD_ID() function to get the Performance Schema thread ID assigned to a given connection ID. This can be useful whenever we use a function that takes the thread ID as its parameter, but we only know the connection ID.

If no thread ID exists for the connection, then NULL is returned.

The built-in PS_THREAD_ID() function does the same thing as the now deprecated sys.ps_thread_id() function. Therefore, we can call the function without needing to qualify it with sys or making sys our current schema.

Continue reading →

Using the sys.format_path() Function to Dynamically Replace a Subpath with its Equivalent System Variable in MySQL

Posted on by Ian

In MySQL, the sys.format_path() function is a stored function in the sys schema. It accepts a path name, and returns the modified path name after replacing subpaths that match the values of certain system variables.

So we can use the function to dynamically replace part or all of our path with a system variable that matches that path segment.

Continue reading →

Create a CHECK Constraint in MySQL

Posted on by Ian

As of MySQL 8.0.16, we can create CHECK constraints in MySQL.

A CHECK constraint is a type of integrity constraint. It specifies a search condition to check the value being entered into a row. If the value being entered violates the CHECK constraint, then the result of the search condition is FALSE and an error occurs (unless the IGNORE clause is used in the SQL statement, in which case a warning is reported and the offending row is skipped).

The ability to create CHECK constraints was introduced in MySQL 8.0.16. Prior to version 8.0.16, MySQL actually allowed a limited version of CHECK constraint syntax, but it was completely ignored (no CHECK constraint was created or evaluated).

Continue reading →

Understanding the sys.ps_is_thread_instrumented() Function in MySQL

Posted on by Ian

In MySQL, we can use the sys.ps_is_thread_instrumented() function to check whether Performance Schema instrumentation for a given connection ID is enabled.

The function returns YES or NO, depending on whether the instrumentation is enabled for the given connection. It can also return UNKNOWN if the connection ID is unknown, and NULL if the connection ID is NULL.

Continue reading →