Wiki Snippets

CREATE PROCEDURE example1() label1:BEGIN DECLARE rowCount INT DEFAULT 0; DECLARE i INT DEFAULT 0;   SELECT COUNT(*) INTO rowCount FROM my_table;   WHILE (i <= rowCount) DO   SELECT i;   SET i = i+1000; END WHILE; END;

The difference with WHILE is that it execute statement before checking the condition. As a result, it iterates one more time than WHILE does. CREATE PROCEDURE example2() label1:BEGIN DECLARE rowCount INT DEFAULT 0; DECLARE i INT DEFAULT 0;   SELECT COUNT(*) INTO rowCount FROM my_table;   REPEAT   SELECT i;   SET i = i+1000; […]

In MySQL, function is different than procedure by returning the value and can be called within SELECT statement. DETERMINISTIC means that the function always produces same results for the same input parameters. CREATE FUNCTION `Sample`(parameter VARCHAR(10)) RETURNS TINYINT(3) UNSIGNED DETERMINISTIC BEGIN DECLARE myfunctionID TINYINT UNSIGNED;   IF parameter = ‘sample1’ THEN SET returnID = 1; […]

SELECT COUNT(*) FROM information_schema.TABLES WHERE table_schema = ‘databasename’;

Former practice: CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci; Current practice: CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

  CREATE DEFINER=`root`@`localhost` PROCEDURE `Sample`(IN inParameterID VARCHAR(10)) Sample:BEGIN   DECLARE httpStatus INT DEFAULT 500; DECLARE errorCode INT DEFAULT NULL; DECLARE errorDetails TEXT DEFAULT NULL;   DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING BEGIN ROLLBACK; SELECT 500 AS statusCode, ‘Failed’ AS statusMessage; END;   SET @variableID = 0;   SELECT TableA.ID INTO @variableID FROM TableA WHERE TableA.ParameterID […]

set profiling=1; call functionname(@functionparam); show profiles;

In mySQL empty or ” is differrent than NULL. Empty means empty string but NULL means it is not set at all, not exist.

UPDATE wp_options SET option_value = REPLACE(option_value, ‘http://www.oldurl’, ‘http://www.newurl’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;   UPDATE wp_posts SET guid = REPLACE(guid, ‘http://www.oldurl’,’http://www.newurl’);   UPDATE wp_posts SET post_content = REPLACE(post_content, ‘http://www.oldurl’, ‘http://www.newurl’);   UPDATE wp_postmeta SET meta_value = REPLACE(meta_value,’http://www.oldurl’,’http://www.newurl’);

Run the following statement for bulk UPDATE UPDATE table1 SET column2 = CASE WHEN column1 = ‘A’ THEN ‘AB’ WHEN column1 = ‘B’ THEN ‘BC’ WHEN column1 = ‘C’ THEN ‘CD’ ELSE column2 END; Make sure you included ELSE statement, if you skip it then it will NULL rest of the column2 fields. So, if […]