MySQL

How to Set or Reset MySQL Root Password

Posted on
Before MySQL 5.7 For the first time to reset root password before MySQL 5.7, you set the new password by mysqladmin: On Windows.C:Usersed>mysqladmin -u’root’ password New password: ******** Confirm new password: ******** On Linux.[root@test ~]# mysqladmin -u root -p password New password: ******** Confirm new password: ******** At a later time, you might want to […]
MySQL

MySQL Regular Expression Replace?

Posted on
For string processing, the databases can not perform as good as middlewares. For example, you may see every kind of regular expression related functions equipped in PHP everywhere, but you can only see two compare operators related to regular expression in MySQL.regexprlike: a synonym of regexp.That is, there’s no built-in function of regular expression replace […]
MySQL

How to Count Occurrences of a String in MySQL

Posted on
Since there’s no function to count occurrences in strings, so I create a customized function to meet the requirement as followings:create function count_occurrences_case_sensitive(whole_string varchar(2048), match_string varchar(255)) returns int(11)begin return round((char_length(whole_string) – char_length(replace(whole_string, match_string, ”))) / char_length(match_string));end;Please note that, this function is multi-byte safe and case-sensitive because the function char_length() and replace() are multi-byte safe in nature, […]
MySQL

A Pitfall of INSERT IGNORE

Posted on
INSERT IGNORE is primarily used for preventing statements stopped from unique constraint violations by ignoring current duplicated rows. That is to say, the duplicated rows will not be inserted into the table.But actually, INSERT IGNORE will try everything it can do to insert the table without errors during statement execution. Let’s see an example. Suppose […]
MySQL

How to Split Strings into Rows in MySQL

Posted on
Suppose we have a table named teams like this:mysql> select * from teams;+—-+——————-+| id | name              |+—-+——————-+|  1 | New York Yankees  ||  2 | Seattle Mariners  ||  3 | Chicago White Sox ||  4 | New York Mets     |+—-+——————-+4 rows in set (0.00 sec)As we can see, […]
MySQL

How to Randomly Select N Rows

Posted on
Suppose there’re 4 kinds of fruit displayed in a supermarket.mysql> select * from fruits;+—-+——–+| id | name   |+—-+——–+|  1 | apple  ||  2 | banana ||  3 | cherry ||  4 | mango  |+—-+——–+4 rows in set (0.00 sec)A mother asks you to select 2 fruits randomly for her from the list. In such […]
MySQL

Partition Keys Can Make Unique Indexes Useless

Posted on
Normally, we build an unique index for protecting the table from inserting duplicate values of combined columns. If the constraint of the unique index is always unique, extremely unique, it might become useless for your business logic. Worse, it might let some unwanted rows sneak into the dependent table without violations. Next, let me explain […]
MySQL

How to Resolve Error 1712: Index table_name is corrupted

Posted on
Got this error when using mysqldump for backing up a database. At this moment, we can’t do any operations but DDL.mysqldump: Error 1712: Index table_name is corrupted when dumping table `table_name` at row: 0Or met this error when queried the table.ERROR 1712 (HY000): Index table_name is corruptedIn the log, you can also see the errors.[root@test […]