Blog

Check your current sql_mode SELECT @@GLOBAL.sql_mode;          or,  SELECT @@sql_mode; Edit your my.cnf and remove  "ONLY_FULL_GROUP_BY". if you don't have sql_mode, just add sql_mode to .conf. Restart mysql server. sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ...

Update / replace text with mysql. UPDATE tbl_name SET     field_name = REPLACE(field_name,         "string_to_find",         "string_to_replace") WHERE     conditions; where state can be optional. ...

Install mysql sudo apt update sudo apt install mysql-server sudo mysql_secure_installation ...

Tip for search in MySQL with case sensitive:  SELECT *  FROM table_name WHERE BINARY field_name LIKE '%search_string%'; ...

Use set_charset to turn current charset to utf8, and you will be able to display Asian fonts.   $db_link = mysqli_connect($db['host'],$db['user'],$db['pass'],$db['db']); //  echo "Initial character set is: " . $db_link -> character_set_name();   $db_link -> set_charset("utf8"); // Change character set to utf8 //  echo "Current character set is: " . $db_link -> character_set_name(); ...

Use SQL_CALC_FOUND_ROWS in your SELECT query and then run SELECT FOUND_ROWS() after that: SELECT SQL_CALC_FOUND_ROWS field_name,filed_name2 FROM tables WHERE CLAUSE LIMIT X; SELECT FOUND_ROWS() ; ...

Remove or revoke DB access or privileges from mysql user both local and remote: REVOKE ALL PRIVILEGES ON *.* FROM 'plugins_dbuser'@'localhost'; REVOKE ALL PRIVILEGES ON *.* FROM 'plugins_dbuser'@'%'; ...

here is how we can replace partial of the string on all rows in MySQL:  UPDATE my_table_name SET my_field_name = REPLACE(my_field_name, 'unwanted-string', 'new-string'); ...

Merge two user groups users into one current group, and delete the second group. Note: some users might be in both groups already. # Show users in groupID 111 are also in groupID 222 SELECT user_id FROM user_group  WHERE user_group_id={111} AND user_id IN (SELECT user_id FROM user_group WHERE user_group_id={222} )  # Change user_group_id 222 to 111. Use 'UPDATE IGNORE'  assume user_id & user_group_id are unique index UPDATE IGNORE user_group  SET user_group_id={111} WHERE...