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 […]
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 […]
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, […]
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 […]
I am not exaggerating the size of the problem. You may think all the non-duplicated rows can always pass through the check from unique constraints and insert into the table eventually, but in fact, they sometimes failed under specific conditions.Allow me to ask you question first. Do you know there’s length limit for every column […]
I think you might have used a group function called group_concat() in your SQL statement as I did and saw only 1024 characters returned in the result. And yes, there’s a length limit 1024 by default imposed on this function.Let’s see what the document say about this:The result is truncated to the maximum length that is […]
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, […]
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 […]
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 […]
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 […]
