Crucial Web Hosting

mysql -h DBHOST -u DBUSER -pDBPASS DBNAME < dump.sql

Replace text in uppercase with the appropriate information:

• DBHOST - Database hostname, usually localhost
• DBUSER - User with privileges to the database
• DBPASS - Password for the user
• DBNAME - Database name

GRANT ALL PRIVILEGES ON `db_name.table_name` (or .*) TO 'username'@'localhost' IDENTIFIED BY 'password';

UPDATE `tbl_name` SET tbl_field = replace(tbl_field, 'FIND', 'REPLACE');

Change a table's storage engine, or table type, from MyISAM to InnoDB:

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ENGINE=InnoDB;') FROM `information_schema.tables` WHERE ENGINE = 'MYISAM';

ALTER TABLE `tbl_name` AUTO_INCREMENT = 1;

urlencode() and urldecode() as MySQL stored functions.

urlencode()

DELIMITER ;
 
DROP FUNCTION IF EXISTS urlencode;
 
DELIMITER |
 
CREATE FUNCTION urlencode (s VARCHAR(4096)) RETURNS VARCHAR(4096)
DETERMINISTIC 
CONTAINS SQL 
BEGIN
    DECLARE c VARCHAR(4096) DEFAULT '';
    DECLARE pointer INT DEFAULT 1;
    DECLARE s2 VARCHAR(4096) DEFAULT '';
 
    IF ISNULL(s) THEN
        RETURN NULL;
    ELSE
        SET s2 = '';
        WHILE pointer <= length(s) DO
            SET c = MID(s,pointer,1);
            IF c = ' ' THEN
                SET c = '+';
            ELSEIF NOT (ASCII(c) BETWEEN 48 AND 57
                OR 
                    ASCII(c) BETWEEN 65 AND 90
                OR 
                    ASCII(c) BETWEEN 97 AND 122)
                THEN
                    SET c = concat("%",LPAD(CONV(ASCII(c),10,16),2,0));
            END IF;
            SET s2 = CONCAT(s2,c);
            SET pointer = pointer + 1;
        END while;
    END IF;
    RETURN s2;
END;
 
|
 
DELIMITER ;

urldecode()

DROP FUNCTION IF EXISTS urldecode;
 
DELIMITER |
 
CREATE FUNCTION urldecode (s VARCHAR(4096)) RETURNS VARCHAR(4096)
DETERMINISTIC 
CONTAINS SQL 
BEGIN
    DECLARE c VARCHAR(4096) DEFAULT '';
    DECLARE pointer INT DEFAULT 1;
    DECLARE h CHAR(2);
    DECLARE h1 CHAR(1);
    DECLARE h2 CHAR(1);
    DECLARE s2 VARCHAR(4096) DEFAULT '';
 
    IF ISNULL(s) THEN
        RETURN NULL;
    ELSE
        SET s2 = '';
        WHILE pointer <= LENGTH(s) DO
            SET c = MID(s,pointer,1);
            IF c = '+' THEN
                SET c = ' ';
            ELSEIF c = '%' AND pointer + 2 <= LENGTH(s) THEN
                SET h1 = LOWER(MID(s,pointer+1,1));
                SET h2 = LOWER(MID(s,pointer+2,1));
                IF (h1 BETWEEN '0' AND '9' OR h1 BETWEEN 'a' AND 'f')
                    AND
                        (h2 BETWEEN '0' AND '9' OR h2 BETWEEN 'a' AND 'f') 
                    THEN
                        SET h = CONCAT(h1,h2);
                        SET pointer = pointer + 2;
                        SET c = CHAR(CONV(h,16,10));
                    END IF;
                END IF;
                SET s2 = CONCAT(s2,c);
                SET pointer = pointer + 1;
        END while;
    END IF;
    RETURN s2;
END;
 
|
 
DELIMITER ;

multiurlencode()

And now a multiurlencode() function for cleaning up data that resulted from multiple urlencode() passes. This just calls urldecode() recursively until there's nothing to decode.

DELIMITER ;
 
DROP FUNCTION IF EXISTS multiurldecode;
 
DELIMITER |
 
CREATE FUNCTION multiurldecode (s VARCHAR(4096)) RETURNS VARCHAR(4096)
DETERMINISTIC 
CONTAINS SQL 
BEGIN
    DECLARE pr VARCHAR(4096) DEFAULT '';
    IF ISNULL(s) THEN
        RETURN NULL;
    END IF;       
    REPEAT
        SET pr = s;
        SELECT urldecode(s) INTO s;
    UNTIL pr = s END REPEAT;
    RETURN s;
END;
 
|
 
DELIMITER ;