In MySQL, the sys.format_statement() system function reduces the length of a given string/SQL statement to the length stored in the statement_truncate_len configuration option.
This function can be handy for truncating potentially long SQL statements retrieved from Performance Schema tables into a more suitable length (64 characters by default).
Continue readingWhen we create or modify a table in MySQL, we have the option of applying a CHECK constraint against one or more columns. This allows us to check the data before it enters the database. Data can only enter the database if it doesn’t violate the rules in our CHECK constraint.
If the column is a JSON column, we have the option of creating a CHECK constraint that checks that the JSON document doesn’t violate its JSON schema. To do this, we can include the schema in the CHECK constraint.
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.
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 readingIn MySQL, the sys.extract_table_from_file_name() function is a stored function in the sys schema.
It accepts a path name, and returns the path component that represents the table name.
Continue readingIn MySQL, we can use the sys.ps_is_account_enabled() function to check whether Performance Schema instrumentation for a given account is enabled.
The function returns YES or NO, depending on whether or not the instrumentation for the given account is enabled.
In MySQL, the sys.ps_thread_account() function returns the account associated with a given thread ID. It returns the account in the form user_name@host_name.
In MySQL, we can use the sys.ps_is_instrument_default_enabled() function to check whether a given Performance Schema instrument is enabled by default.
The function returns YES or NO, depending on whether the instrument is enabled by default.
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).
In MySQL, we can use the sys.ps_is_instrument_default_timed() function to check whether a given Performance Schema instrument is timed by default.
The function returns YES or NO, depending on whether the instrument is timed by default.