You might have known that LIST, RANGE, and HASH partitions all refuse to accept VARACHAR column as the native partition key.mysql> alter table locations partition… Read More »How to Use String-Typed Columns as Partition Keys
How to Resolve ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
ERROR 1217 (23000) Got a MySQL ERROR 1217 (23000) when tried to partition a table. mysql> alter table geolocations partition by ... ... ERROR 1217… Read More »How to Resolve ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
If you have a very big database containing big tables to be backed up, you might have the same issue with mine: The web server… Read More »How to Minimize the Impact of Mysqldump
You may notice that, when we selected the maximum value (via MAX()) and with other columns, they looked like in the same row, but in fact,… Read More »How to Select Rows Contains Maximum Values
You may put some values which are potentially numeric in a VARCHAR column, and you would like to find them out for more operations. Unfortunately,… Read More »How to Select Numeric Values from a VARCHAR Column
The usual word boundaries in regular expression is b, but MySQL takes POSIX-liked [[:<:]] and [[:>:]] as word boundaries. Now my question is the word… Read More »Do Word Boundaries Match the Beginning and End of a String?
According to MySQL documentation:MySQL :: MySQL 5.7 Reference Manual :: 12.5.2 Regular ExpressionsREGEXP is not case sensitive, except when used with binary strings.It seems to… Read More »Is REGEXP (or RLIKE) Always Case Insensitive?
Duplicating a table actually contains three phases, if you're using CREATE TABLE LIKE (CTL) clause: Metadata duplication. Data duplication. (Optional) Constraints and Triggers duplication. Metadata… Read More »3 Steps to Duplicate a MySQL Table Using CREATE TABLE LIKE
In the following example, we'd like to select the city name which are regular-expressly like (rlike or regexp) the string 'New York+', but the statement failed… Read More »How to Resolve ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp
There're two ways to backup selected tables in MySQL:Positive ListingYou can backup tables by positively and explicitly listing all necessary ones. The syntax is as… Read More »How to Backup Selected Tables by mysqldump