How to mass export Tasks from Windows Task Scheduler

Windows Task Scheduler holds all the tasks which were created by an application to periodically check for updates or by you to send an email when somebody logs in. If you need to create the same task on another Windows 10 computer or need them back after reinstallation, there is away. Task Scheduler gives you an option to export them one by one but not all of them together. In this tutorial, I will tell you a trick on how you can mass export Tasks from Windows Task Scheduler, and import it back to Windows.

How to mass export Tasks from Windows Task Scheduler

All the Tasks created in Windows 10 are available in a Task Folder. It is available in the Windows System Directory which in my case is C:\Windows\System32\Tasks.

1. Navigate to Tasks Folder. You will be prompted for admin permission.
2. Here you will see a couple of folders with some files inside it. They represent the same folder structure of the Task Scheduler and files too have the same name.
3. Copy the task files to the machine you want to export. You can also use it to back up your tasks in case your computer needs a reinstallation. It saves time as you don’t have to create them again.
4. Rename the files with extension as “.XML”. It is the same extension Task Scheduler uses when you export the tasks.
5. Once renamed, now use the Task Scheduler Import task feature to import them one by one. Windows will not recognize them if you drop them directly into the Tasks Folder of Windows.

I wish the mass export and import feature in Windows 10. It will be helpful for many people, especially in Enterprise.

Video Tutorial to mass export Tasks from Windows Task Scheduler

It’s an excellent way to import and export tasks, especially if you want to run the same tasks on multiple PC. Also, it opens up the option to keep a copy of all the tasks when you are reinstalling Windows. All you need to do is copy it again, and it restores all the tasks.

Be aware that if Tasks have file paths it may change when switch computers. Make sure to visit each task, and fix the path, and program names as required.

I hope the post was easy to follow, and you were able to mass export Tasks from Windows Task Scheduler, and later import it back to the same Windows 10 PC or any other PC.

 

Windows: `Touch` Command – Equivalent

Posted on October 28, 2020by admin

The touch command in Linux is used to change a file’s “Access“, “Modify” and “Change” timestamps to the current time and date, but if the file doesn’t exist, the touch command creates it.

If you simply want to create an empty file from the command-line prompt (CMD) or a Windows PowerShell – the type and copy commands can be considered as a Windows touch command equivalent.

The file timestamps in Windows can be changed using the built-in PowerShell commands.

Cool Tip: Windows cat command equivalent in CMD and PowerShell! Read more →

Windows `Touch` Command Equivalent

To create a new file, as a Windows touch equivalent, you can use one of these commands:

C:\> type nul >> "file.txt"
- or -
C:\> copy nul "file.txt"

To change a file timestamps to the current time and date, execute the following commands from the PowerShell:

PS C:\> (Get-Item "file.txt").CreationTime=$(Get-Date -format o)
PS C:\> (Get-Item "file.txt").LastWriteTime=$(Get-Date -format o)
PS C:\> (Get-Item "file.txt").LastAccessTime=$(Get-Date -format o)

Cool Tip: Windows grep command equivalent in CMD and PowerShell! Read more →

To set the specific timestamps, execute:

PS C:\> (Get-Item "file.txt").CreationTime=("01 March 2020 09:00:00")
PS C:\> (Get-Item "file.txt").LastWriteTime=("20 April 2020 17:00:00")
PS C:\> (Get-Item "file.txt").LastAccessTime=("20 April 2020 17:00:00")

The timestamps can be displayed using the following command:

PS C:\> Get-Item file.txt | Format-List CreationTime, LastAccessTime, LastWriteTime

mysql deadlocks with concurrent inserts

mysql deadlocks with concurrent inserts

It is possible to cause deadlocks in mysql (Innodb) on concurrent insert statements, without there being any transactions in progress. Deadlocks are possible even when the inserts don't collide on any key.

The deadlocks occur due to gap locking done by mysql. There are several reasons for gap locking, and in this particular case, it has to do with preserving a unique key constraint on an index. The situation presents itself to us this way: There is a unique key constraint on a column and we are doing an insert. Mysql has to make sure that the lock it takes is sufficient to prevent another concurrent insert from adding a record with the same key, thus breaking the unique key constraint.

Mysql innodb engine performs row locking on inserts. If column A has a unique key constraint, and we are adding the value "bbb" for column A in an insert statement, mysql needs to lock any gap in the index between the two current records where "bbb" will be inserted at.

To illustrate the deadlock, let us start with a table schema:

TABLE vegetable (
   id bigint(10) NOT NULL auto_increment,
   name varchar(255) NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY uk_name (name)
) ENGINE=InnoDB

Let us assume the existence of these records in the table, and look at them in 'name' index order:

id name
10 ggg
05 jjj

Now, imagine two concurrent connections executing the following inserts in the following order:

Connection 1:

insert ignore into vegetable values(null, "ppp");

For this insert to proceed, connection 1 will lock the gap between "jjj" and "ppp" in the name index.

Connection 2:

insert ignore into vegetable values (null,"iii");

This will require locking the gap after "ggg", upto "iii". Since the lock from connection 1 does not span this, it will succeed.

insert ignore into vegetable values (null, "mmm");

This needs to lock the gap after "jjj" upto "mmm". Since connection 1 has a lock between "jjj" and "ppp", effectively spanning the lock connection 2 is attempting to take, this will block.

Connection 1:

insert ignore into vegetable values (null, "hhh");

This requires the gap lock between "ggg" and "hhh". This will block as it spans the the lock ["ggg" to "iii"] held by connection 2.


Thus we have both connections blocked on each other. This is the deadlock.

Here is a diagram. Transactions to the left are done by Connection 2. Transactions to the right are done by Connection 1. The sequence of transactions is donated by numbers 1) through 4).

Connection 2                         Connection1

---------------------------  ggg
G                                           G
A                                           AP
P                                            <------------------- 4) hhh
Lock                                                                  blocks (deadlock)
2) iii -------------------->

---------------------------  jjj 
G                                           G
A
P                                            A
Lock
3) mmm --------------->            P  
blocks
                                              L
                                              o
                                              c
                                              k
                                             <--------------------- 1) ppp

You can avoid this if you can order the inserts on each connection on the same direction. The deadlock happens as connection 2 inserts in ascending order of the index, while connection 1 inserts on descending order.

If you can't do this for practical reasons, you could retry the operation. Unless there is a high level of concurrency with a high load on the db where each transaction takes a heavy hit, a simple retry should work.

how to resolve "InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table" in mysql

how to resolve "InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table" in mysql

 

I am continuous getting above error in mysqld log. Please tell me what is this error about and how to solve it. I am using mysql 5.7 (not mariadb)

• mysql
• innodb

 

 

A guess: A FULLTEXT index on that table is corrupted.

ALTER TABLE tablename ENGINE=InnoDB;

 

Fixing Error: InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

1

This error appears in your error log. It means there is a duplicate key in your full text index, sadly it doesn’t tell you which table or which full text index.

HINT for MySQL/MariaDB/Percona devs: just add the index_id to the error message.

SQL to find all text indexes in your db:

select table_schema,table_name,index_name, index_type from information_schema.statistics
where index_type = 'FULLTEXT' order by table_schema;

+-----------------+-----------------------+-------------+------------+
| table_schema    | table_name            | index_name  | index_type |
+-----------------+-----------------------+-------------+------------+
| db0001_fullname | Activity_fullname_log | message     | FULLTEXT   |
| db0002_fullname | Activity_fullname_log | message     | FULLTEXT   |
+-----------------+-----------------------+-------------+------------+
2 rows in set (0.715 sec)

So what is the next step?

If you follow the documentation it will tell you to rebuild the table or use OPTIMIZE TABLE with innodb_optimize_fulltext_only=ON to rebuild just the text index.

https://dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html#fulltext-rebuild-innodb-indexes

https://dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html#fulltext-optimize

My recommendation: Rebuild the table (including the full text indexes)

-- Plain old ALTER TABLE
use db0001_fullname;
ALTER ONLINE TABLE Activity_fullname_log ENGINE=INNODB;

-- Or pt-osc equivalent

pt-online-schema-change --dry-run --alter="ENGINE=INNODB" \
--alter-foreign-keys-method=rebuild_constraints --max-lag=60 \
--max-load Threads_running=50 --critical-load Threads_running=60 \
h=localhost,D=db0001_fullname,t=Activity_fullname_log

pt-online-schema-change --execute --alter="ENGINE=INNODB" \
--alter-foreign-keys-method=rebuild_constraints --max-lag=60 \
--max-load Threads_running=50 --critical-load Threads_running=60 \
h=localhost,D=db0001_fullname,t=Activity_fullname_log

The OPTIMIZE TABLE method:

The trouble with this method is it will only do a set number of words per execution of OPTIMIZE TABLE.

SQL to set the specific table so you can query INNODB_FT_INDEX_TABLE

set global innodb_ft_aux_table = 'db0001_fullname/Activity_fullname_log';
Query OK, 0 rows affected (0.002 sec)

SQL to find the number of distinct words in a specific text index:

SELECT count(distinct word) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+----------------------+
| count(distinct word) |
+----------------------+
|               600000 |
+----------------------+
1 row in set (1 min 53.333 sec)

Depending how much impact you want per OPTIMIZE TABLE set innodb_ft_num_word_optimize appropriately.

The remaining steps to rebuilding your full text index using OPTIMIZE TABLE

set GLOBAL innodb_ft_num_word_optimize=10000;
set GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE Activity_fullname_log; 
OPTIMIZE TABLE Activity_fullname_log;
OPTIMIZE TABLE Activity_fullname_log;
OPTIMIZE TABLE Activity_fullname_log;
OPTIMIZE TABLE Activity_fullname_log;
... -- repeat round(distinct words/innodb_ft_num_word_optimize)+1
OPTIMIZE TABLE Activity_fullname_log;

MySQL Regular Expressions

 

12.8.2 Regular Expressions

Table 12.14 Regular Expression Functions and Operators

Name	Description
NOT REGEXP	Negation of REGEXP
REGEXP	Whether string matches regular expression
REGEXP_INSTR()	Starting index of substring matching regular expression
REGEXP_LIKE()	Whether string matches regular expression
REGEXP_REPLACE()	Replace substrings matching regular expression
REGEXP_SUBSTR()	Return substring matching regular expression
RLIKE	Whether string matches regular expression

A regular expression is a powerful way of specifying a pattern for a complex search. This section discusses the functions and operators available for regular expression matching and illustrates, with examples, some of the special characters and constructs that can be used for regular expression operations. See also Section 3.3.4.7, “Pattern Matching”.

MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. (Prior to MySQL 8.0.4, MySQL used Henry Spencer's implementation of regular expressions, which operates in byte-wise fashion and is not multibyte safe. For information about ways in which applications that use regular expressions may be affected by the implementation change, see Regular Expression Compatibility Considerations.)

Prior to MySQL 8.0.22, it was possible to use binary string arguments with these functions, but they yielded inconsistent results. In MySQL 8.0.22 and later, use of a binary string with any of the MySQL regular expression functions is rejected with ER_CHARACTER_SET_MISMATCH.

• Regular Expression Function and Operator Descriptions

• Regular Expression Syntax

• Regular Expression Resource Control

• Regular Expression Compatibility Considerations

Regular Expression Function and Operator Descriptions

• expr NOT REGEXP pat, expr NOT RLIKE pat

  This is the same as NOT (expr REGEXP pat).

• expr REGEXP pat, expr RLIKE pat

  Returns 1 if the string expr matches the regular expression specified by the pattern pat, 0 otherwise. If expr or pat is NULL, the return value is NULL.

  REGEXP and RLIKE are synonyms for REGEXP_LIKE().

  For additional information about how matching occurs, see the description for REGEXP_LIKE().

  mysql> SELECT 'Michael!' REGEXP '.*';
  +------------------------+
  | 'Michael!' REGEXP '.*' |
  +------------------------+
  |                      1 |
  +------------------------+
  mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
  +---------------------------------------+
  | 'new*\n*line' REGEXP 'new\\*.\\*line' |
  +---------------------------------------+
  |                                     0 |
  +---------------------------------------+
  mysql> SELECT 'a' REGEXP '^[a-d]';
  +---------------------+
  | 'a' REGEXP '^[a-d]' |
  +---------------------+
  |                   1 |
  +---------------------+

• REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])

  Returns the starting index of the substring of the string expr that matches the regular expression specified by the pattern pat, 0 if there is no match. If expr or pat is NULL, the return value is NULL. Character indexes begin at 1.

  REGEXP_INSTR() takes these optional arguments:

  • pos: The position in expr at which to start the search. If omitted, the default is 1.

  • occurrence: Which occurrence of a match to search for. If omitted, the default is 1.

  • return_option: Which type of position to return. If this value is 0, REGEXP_INSTR() returns the position of the matched substring's first character. If this value is 1, REGEXP_INSTR() returns the position following the matched substring. If omitted, the default is 0.

  • match_type: A string that specifies how to perform matching. The meaning is as described for REGEXP_LIKE().

  For additional information about how matching occurs, see the description for REGEXP_LIKE().

  mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog');
  +------------------------------------+
  | REGEXP_INSTR('dog cat dog', 'dog') |
  +------------------------------------+
  |                                  1 |
  +------------------------------------+
  mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);
  +---------------------------------------+
  | REGEXP_INSTR('dog cat dog', 'dog', 2) |
  +---------------------------------------+
  |                                     9 |
  +---------------------------------------+
  mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}');
  +-------------------------------------+
  | REGEXP_INSTR('aa aaa aaaa', 'a{2}') |
  +-------------------------------------+
  |                                   1 |
  +-------------------------------------+
  mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
  +-------------------------------------+
  | REGEXP_INSTR('aa aaa aaaa', 'a{4}') |
  +-------------------------------------+
  |                                   8 |
  +-------------------------------------+

• REGEXP_LIKE(expr, pat[, match_type])

  Returns 1 if the string expr matches the regular expression specified by the pattern pat, 0 otherwise. If expr or pat is NULL, the return value is NULL.

  The pattern can be an extended regular expression, the syntax for which is discussed in Regular Expression Syntax. The pattern need not be a literal string. For example, it can be specified as a string expression or table column.

  The optional match_type argument is a string that may contain any or all the following characters specifying how to perform matching:

  • c: Case-sensitive matching.

  • i: Case-insensitive matching.

  • m: Multiple-line mode. Recognize line terminators within the string. The default behavior is to match line terminators only at the start and end of the string expression.

  • n: The . character matches line terminators. The default is for . matching to stop at the end of a line.

  • u: Unix-only line endings. Only the newline character is recognized as a line ending by the ., ^, and $ match operators.

  If characters specifying contradictory options are specified within match_type, the rightmost one takes precedence.

  By default, regular expression operations use the character set and collation of the expr and pat arguments when deciding the type of a character and performing the comparison. If the arguments have different character sets or collations, coercibility rules apply as described in Section 10.8.4, “Collation Coercibility in Expressions”. Arguments may be specified with explicit collation indicators to change comparison behavior.

  mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');
  +---------------------------------------+
  | REGEXP_LIKE('CamelCase', 'CAMELCASE') |
  +---------------------------------------+
  |                                     1 |
  +---------------------------------------+
  mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs);
  +------------------------------------------------------------------+
  | REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs) |
  +------------------------------------------------------------------+
  |                                                                0 |
  +------------------------------------------------------------------+

  match_type may be specified with the c or i characters to override the default case sensitivity. Exception: If either argument is a binary string, the arguments are handled in case-sensitive fashion as binary strings, even if match_type contains the i character.

  Note

  MySQL uses C escape syntax in strings (for example, \n to represent the newline character). If you want your expr or pat argument to contain a literal \, you must double it. (Unless the NO_BACKSLASH_ESCAPES SQL mode is enabled, in which case no escape character is used.)

  mysql> SELECT REGEXP_LIKE('Michael!', '.*');
  +-------------------------------+
  | REGEXP_LIKE('Michael!', '.*') |
  +-------------------------------+
  |                             1 |
  +-------------------------------+
  mysql> SELECT REGEXP_LIKE('new*\n*line', 'new\\*.\\*line');
  +----------------------------------------------+
  | REGEXP_LIKE('new*\n*line', 'new\\*.\\*line') |
  +----------------------------------------------+
  |                                            0 |
  +----------------------------------------------+
  mysql> SELECT REGEXP_LIKE('a', '^[a-d]');
  +----------------------------+
  | REGEXP_LIKE('a', '^[a-d]') |
  +----------------------------+
  |                          1 |
  +----------------------------+

  mysql> SELECT REGEXP_LIKE('abc', 'ABC');
  +---------------------------+
  | REGEXP_LIKE('abc', 'ABC') |
  +---------------------------+
  |                         1 |
  +---------------------------+
  mysql> SELECT REGEXP_LIKE('abc', 'ABC', 'c');
  +--------------------------------+
  | REGEXP_LIKE('abc', 'ABC', 'c') |
  +--------------------------------+
  |                              0 |
  +--------------------------------+

• REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

  Replaces occurrences in the string expr that match the regular expression specified by the pattern pat with the replacement string repl, and returns the resulting string. If expr, pat, or repl is NULL, the return value is NULL.

  REGEXP_REPLACE() takes these optional arguments:

  • pos: The position in expr at which to start the search. If omitted, the default is 1.

  • occurrence: Which occurrence of a match to replace. If omitted, the default is 0 (which means “replace all occurrences”).

  • match_type: A string that specifies how to perform matching. The meaning is as described for REGEXP_LIKE().

  Prior to MySQL 8.0.17, the result returned by this function used the UTF-16 character set; in MySQL 8.0.17 and later, the character set and collation of the expression searched for matches is used. (Bug #94203, Bug #29308212)

  For additional information about how matching occurs, see the description for REGEXP_LIKE().

  mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
  +-----------------------------------+
  | REGEXP_REPLACE('a b c', 'b', 'X') |
  +-----------------------------------+
  | a X c                             |
  +-----------------------------------+
  mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
  +----------------------------------------------------+
  | REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3) |
  +----------------------------------------------------+
  | abc def X                                          |
  +----------------------------------------------------+

• REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])

  Returns the substring of the string expr that matches the regular expression specified by the pattern pat, NULL if there is no match. If expr or pat is NULL, the return value is NULL.

  REGEXP_SUBSTR() takes these optional arguments:

  • pos: The position in expr at which to start the search. If omitted, the default is 1.

  • occurrence: Which occurrence of a match to search for. If omitted, the default is 1.

  • match_type: A string that specifies how to perform matching. The meaning is as described for REGEXP_LIKE().

  Prior to MySQL 8.0.17, the result returned by this function used the UTF-16 character set; in MySQL 8.0.17 and later, the character set and collation of the expression searched for matches is used. (Bug #94203, Bug #29308212)

  For additional information about how matching occurs, see the description for REGEXP_LIKE().

  mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');
  +----------------------------------------+
  | REGEXP_SUBSTR('abc def ghi', '[a-z]+') |
  +----------------------------------------+
  | abc                                    |
  +----------------------------------------+
  mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3);
  +----------------------------------------------+
  | REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) |
  +----------------------------------------------+
  | ghi                                          |
  +----------------------------------------------+

Regular Expression Syntax

A regular expression describes a set of strings. The simplest regular expression is one that has no special characters in it. For example, the regular expression hello matches hello and nothing else.

Nontrivial regular expressions use certain special constructs so that they can match more than one string. For example, the regular expression hello|world contains the | alternation operator and matches either the hello or world.

As a more complex example, the regular expression B[an]*s matches any of the strings Bananas, Baaaaas, Bs, and any other string starting with a B, ending with an s, and containing any number of a or n characters in between.

The following list covers some of the basic special characters and constructs that can be used in regular expressions. For information about the full regular expression syntax supported by the ICU library used to implement regular expression support, visit the International Components for Unicode web site.

• ^

  Match the beginning of a string.

  mysql> SELECT REGEXP_LIKE('fo\nfo', '^fo$');                   -> 0
  mysql> SELECT REGEXP_LIKE('fofo', '^fo');                      -> 1

• $

  Match the end of a string.

  mysql> SELECT REGEXP_LIKE('fo\no', '^fo\no$');                 -> 1
  mysql> SELECT REGEXP_LIKE('fo\no', '^fo$');                    -> 0

• .

  Match any character (including carriage return and newline, although to match these in the middle of a string, the m (multiple line) match-control character or the (?m) within-pattern modifier must be given).

  mysql> SELECT REGEXP_LIKE('fofo', '^f.*$');                    -> 1
  mysql> SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$');                -> 0
  mysql> SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$', 'm');           -> 1
  mysql> SELECT REGEXP_LIKE('fo\r\nfo', '(?m)^f.*$');           -> 1

• a*

  Match any sequence of zero or more a characters.

  mysql> SELECT REGEXP_LIKE('Ban', '^Ba*n');                     -> 1
  mysql> SELECT REGEXP_LIKE('Baaan', '^Ba*n');                   -> 1
  mysql> SELECT REGEXP_LIKE('Bn', '^Ba*n');                      -> 1

• a+

  Match any sequence of one or more a characters.

  mysql> SELECT REGEXP_LIKE('Ban', '^Ba+n');                     -> 1
  mysql> SELECT REGEXP_LIKE('Bn', '^Ba+n');                      -> 0

• a?

  Match either zero or one a character.

  mysql> SELECT REGEXP_LIKE('Bn', '^Ba?n');                      -> 1
  mysql> SELECT REGEXP_LIKE('Ban', '^Ba?n');                     -> 1
  mysql> SELECT REGEXP_LIKE('Baan', '^Ba?n');                    -> 0

• de|abc

  Alternation; match either of the sequences de or abc.

  mysql> SELECT REGEXP_LIKE('pi', 'pi|apa');                     -> 1
  mysql> SELECT REGEXP_LIKE('axe', 'pi|apa');                    -> 0
  mysql> SELECT REGEXP_LIKE('apa', 'pi|apa');                    -> 1
  mysql> SELECT REGEXP_LIKE('apa', '^(pi|apa)$');                -> 1
  mysql> SELECT REGEXP_LIKE('pi', '^(pi|apa)$');                 -> 1
  mysql> SELECT REGEXP_LIKE('pix', '^(pi|apa)$');                -> 0

• (abc)*

  Match zero or more instances of the sequence abc.

  mysql> SELECT REGEXP_LIKE('pi', '^(pi)*$');                    -> 1
  mysql> SELECT REGEXP_LIKE('pip', '^(pi)*$');                   -> 0
  mysql> SELECT REGEXP_LIKE('pipi', '^(pi)*$');                  -> 1

• {1}, {2,3}

  Repetition; {n} and {m,n} notation provide a more general way of writing regular expressions that match many occurrences of the previous atom (or “piece”) of the pattern. m and n are integers.

  • a*

    Can be written as a{0,}.

  • a+

    Can be written as a{1,}.

  • a?

    Can be written as a{0,1}.

  To be more precise, a{n} matches exactly n instances of a. a{n,} matches n or more instances of a. a{m,n} matches m through n instances of a, inclusive. If both m and n are given, m must be less than or equal to n.

  mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{2}e');              -> 0
  mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{3}e');              -> 1
  mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{1,10}e');           -> 1

• [a-dX], [^a-dX]

  Matches any character that is (or is not, if ^ is used) either a, b, c, d or X. A - character between two other characters forms a range that matches all characters from the first character to the second. For example, [0-9] matches any decimal digit. To include a literal ] character, it must immediately follow the opening bracket [. To include a literal - character, it must be written first or last. Any character that does not have a defined special meaning inside a [] pair matches only itself.

  mysql> SELECT REGEXP_LIKE('aXbc', '[a-dXYZ]');                 -> 1
  mysql> SELECT REGEXP_LIKE('aXbc', '^[a-dXYZ]$');               -> 0
  mysql> SELECT REGEXP_LIKE('aXbc', '^[a-dXYZ]+$');              -> 1
  mysql> SELECT REGEXP_LIKE('aXbc', '^[^a-dXYZ]+$');             -> 0
  mysql> SELECT REGEXP_LIKE('gheis', '^[^a-dXYZ]+$');            -> 1
  mysql> SELECT REGEXP_LIKE('gheisa', '^[^a-dXYZ]+$');           -> 0

• [=character_class=]

  Within a bracket expression (written using [ and ]), [=character_class=] represents an equivalence class. It matches all characters with the same collation value, including itself. For example, if o and (+) are the members of an equivalence class, [[=o=]], [[=(+)=]], and [o(+)] are all synonymous. An equivalence class may not be used as an endpoint of a range.

• [:character_class:]

  Within a bracket expression (written using [ and ]), [:character_class:] represents a character class that matches all characters belonging to that class. The following table lists the standard class names. These names stand for the character classes defined in the ctype(3) manual page. A particular locale may provide other class names. A character class may not be used as an endpoint of a range.

  Character Class Name	Meaning
  alnum	Alphanumeric characters
  alpha	Alphabetic characters
  blank	Whitespace characters
  cntrl	Control characters
  digit	Digit characters
  graph	Graphic characters
  lower	Lowercase alphabetic characters
  print	Graphic or space characters
  punct	Punctuation characters
  space	Space, tab, newline, and carriage return
  upper	Uppercase alphabetic characters
  xdigit	Hexadecimal digit characters

  mysql> SELECT REGEXP_LIKE('justalnums', '[[:alnum:]]+');       -> 1
  mysql> SELECT REGEXP_LIKE('!!', '[[:alnum:]]+');               -> 0

To use a literal instance of a special character in a regular expression, precede it by two backslash (\) characters. The MySQL parser interprets one of the backslashes, and the regular expression library interprets the other. For example, to match the string 1+2 that contains the special + character, only the last of the following regular expressions is the correct one:

mysql> SELECT REGEXP_LIKE('1+2', '1+2');                       -> 0
mysql> SELECT REGEXP_LIKE('1+2', '1\+2');                      -> 0
mysql> SELECT REGEXP_LIKE('1+2', '1\\+2');                     -> 1

Regular Expression Resource Control

REGEXP_LIKE() and similar functions use resources that can be controlled by setting system variables:

• The match engine uses memory for its internal stack. To control the maximum available memory for the stack in bytes, set the regexp_stack_limit system variable.

• The match engine operates in steps. To control the maximum number of steps performed by the engine (and thus indirectly the execution time), set the regexp_time_limit system variable. Because this limit is expressed as number of steps, it affects execution time only indirectly. Typically, it is on the order of milliseconds.

Regular Expression Compatibility Considerations

Prior to MySQL 8.0.4, MySQL used the Henry Spencer regular expression library to support regular expression operations, rather than International Components for Unicode (ICU). The following discussion describes differences between the Spencer and ICU libraries that may affect applications:

• With the Spencer library, the REGEXP and RLIKE operators work in byte-wise fashion, so they are not multibyte safe and may produce unexpected results with multibyte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal.

  ICU has full Unicode support and is multibyte safe. Its regular expression functions treat all strings as UTF-16. You should keep in mind that positional indexes are based on 16-bit chunks and not on code points. This means that, when passed to such functions, characters using more than one chunk may produce unanticipated results, such as those shown here:

  mysql> SELECT REGEXP_INSTR('🍣🍣b', 'b');
  +--------------------------+
  | REGEXP_INSTR('??b', 'b') |
  +--------------------------+
  |                        5 |
  +--------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT REGEXP_INSTR('🍣🍣bxxx', 'b', 4);
  +--------------------------------+
  | REGEXP_INSTR('??bxxx', 'b', 4) |
  +--------------------------------+
  |                              5 |
  +--------------------------------+
  1 row in set (0.00 sec)

  Characters within the Unicode Basic Multilingual Plane, which includes characters used by most modern languages, are safe in this regard:

  mysql> SELECT REGEXP_INSTR('бжb', 'b');
  +----------------------------+
  | REGEXP_INSTR('бжb', 'b')   |
  +----------------------------+
  |                          3 |
  +----------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT REGEXP_INSTR('עבb', 'b');
  +----------------------------+
  | REGEXP_INSTR('עבb', 'b')   |
  +----------------------------+
  |                          3 |
  +----------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT REGEXP_INSTR('µå周çб', '周');
  +------------------------------------+
  | REGEXP_INSTR('µå周çб', '周')       |
  +------------------------------------+
  |                                  3 |
  +------------------------------------+
  1 row in set (0.00 sec)

  Emoji, such as the “sushi” character 🍣 (U+1F363) used in the first two examples, are not included in the Basic Multilingual Plane, but rather in Unicode's Supplementary Multilingual Plane. Another issue can arise with emoji and other 4-byte characters when REGEXP_SUBSTR() or a similar function begins searching in the middle of a character. Each of the two statements in the following example starts from the second 2-byte position in the first argument. The first statement works on a string consisting solely of 2-byte (BMP) characters. The second statement contains 4-byte characters which are incorrectly interpreted in the result because the first two bytes are stripped off and so the remainder of the character data is misaligned.

  mysql> SELECT REGEXP_SUBSTR('周周周周', '.*', 2);
  +----------------------------------------+
  | REGEXP_SUBSTR('周周周周', '.*', 2)     |
  +----------------------------------------+
  | 周周周                                 |
  +----------------------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT REGEXP_SUBSTR('🍣🍣🍣🍣', '.*', 2);
  +--------------------------------+
  | REGEXP_SUBSTR('????', '.*', 2) |
  +--------------------------------+
  | ?㳟揘㳟揘㳟揘                  |
  +--------------------------------+
  1 row in set (0.00 sec)

• For the . operator, the Spencer library matches line-terminator characters (carriage return, newline) anywhere in string expressions, including in the middle. To match line terminator characters in the middle of strings with ICU, specify the m match-control character.

• The Spencer library supports word-beginning and word-end boundary markers ([[:<:]] and [[:>:]] notation). ICU does not. For ICU, you can use \b to match word boundaries; double the backslash because MySQL interprets it as the escape character within strings.

• The Spencer library supports collating element bracket expressions ([.characters.] notation). ICU does not.

• For repetition counts ({n} and {m,n} notation), the Spencer library has a maximum of 255. ICU has no such limit, although the maximum number of match engine steps can be limited by setting the regexp_time_limit system variable.

• ICU interprets parentheses as metacharacters. To specify a literal open or close parenthesis ( in a regular expression, it must be escaped:

  mysql> SELECT REGEXP_LIKE('(', '(');
  ERROR 3692 (HY000): Mismatched parenthesis in regular expression.
  mysql> SELECT REGEXP_LIKE('(', '\\(');
  +-------------------------+
  | REGEXP_LIKE('(', '\\(') |
  +-------------------------+
  |                       1 |
  +-------------------------+
  mysql> SELECT REGEXP_LIKE(')', ')');
  ERROR 3692 (HY000): Mismatched parenthesis in regular expression.
  mysql> SELECT REGEXP_LIKE(')', '\\)');
  +-------------------------+
  | REGEXP_LIKE(')', '\\)') |
  +-------------------------+
  |                       1 |
  +-------------------------+

• ICU also interprets square brackets as metacharacters, but only the opening square bracket need be escaped to be used as a literal character:

mysql> SELECT REGEXP_LIKE('[', '[');
ERROR 3696 (HY000): The regular expression contains an
unclosed bracket expression.
mysql> SELECT REGEXP_LIKE('[', '\\[');
+-------------------------+
| REGEXP_LIKE('[', '\\[') |
+-------------------------+
|                       1 |
+-------------------------+
mysql> SELECT REGEXP_LIKE(']', ']');
+-----------------------+
| REGEXP_LIKE(']', ']') |
+-----------------------+
|                     1 |
+-----------------------+