# How to do Everything in MySQL/MariaDB

## Useful Tricks

### Convert a Column to Uppercase

UPDATE table\_name SET `column_name` = UPPER( `column_name` )

---

### Show and Change View Definer

```
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';<br></br>SHOW CREATE VIEW [view_name];<br></br>ALTER DEFINER = '[username]'@'[host]' VIEW [view_name] AS [select statement]; 
```

---

### Alter Views

```
ALTER VIEW <view name> AS [view statements]
```

---

### MySQL Datatype for Different Password Hashes

It depends on the hashing algorithm you use. Hashing always produces a result of the same length, regardless of the input. It is typical to represent the binary hash result in text, as a series of hexadecimal digits. Or you can use the \[UNHEX()\]([http://dev.mysql.com/doc/refman/5.5/en/string-](http://dev.mysql.com/doc/refman/5.5/en/string-) functions.html#function\_unhex) function to reduce a string of hex digits by half.

- MD5 generates a 128-bit hash value. You can use CHAR(32) or BINARY(16)
- SHA-1 generates a 160-bit hash value. You can use CHAR(40) or BINARY(20)
- SHA-224 generates a 224-bit hash value. You can use CHAR(56) or BINARY(28)
- SHA-256 generates a 256-bit hash value. You can use CHAR(64) or BINARY(32)
- SHA-384 generates a 384-bit hash value. You can use CHAR(96) or BINARY(48)
- SHA-512 generates a 512-bit hash value. You can use CHAR(128) or BINARY(64)
- BCrypt generates an implementation-dependent 448-bit hash value. [You might need CHAR(56), CHAR(60), CHAR(76), BINARY(56) or BINARY(60)](http://stackoverflow.com/questions/5881169/storing-a-hashed-password-bcrypt-in-a-database-type-length-of-column)

NIST recommends using SHA-256 or higher for passwords. Lesser hashing algorithms have their uses, but they are [known to be crackable](http://www.larc.usp.br/%7Epbarreto/hflounge.html).

You should [salt](http://en.wikipedia.org/wiki/Password_salt) your passwords before applying the hashing function. Salting a password does not affect the length of the hash result.

---

### Chinese Support in MySQL

Convert entire database to UTF-8: `ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;`

Convert entire table to UTF-8: `ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;`

Convert field to UTF-8: `ALTER TABLE tablename MODIFY columnname columndef CHARACTER SET utf8 COLLATE utf8_unicode_ci;`

---

### MySQL Grant Permission

```
Grant all on {dbname}.* to 'Id'@'localhost' identified by 'password'
```

---

### Convert All Table Columns' Charset and Collation

```
ALTER TABLE <table> CONVERT TO CHARACTER SET <charset> COLLATE <collation>;
```

<a name="toc_11"></a>Convert Database to MyISAM

```
#!/bin/sh<br></br><br></br>DB='<DB Name>'<br></br>TABLES=$(/opt/lampp/bin/mysql -uroot --skip-column-names -B -D $DB -e 'show tables')<br></br><br></br>for T in $TABLES<br></br>do<br></br> /opt/lampp/bin/mysql -uroot -D $DB -e "ALTER TABLE $T ENGINE=MYISAM"<br></br>done
```

## Solution to Common Problems

### MariaDB Function Error From mysqldump

Use DELIMITER keyword to change end of function delimiter, e.g.:

```
DELIMITER //<br></br>CREATE FUNCTION counter () RETURNS INT<br></br> BEGIN<br></br> UPDATE counter SET c = c + 1;<br></br> RETURN (SELECT c FROM counter LIMIT 1);<br></br> END;<br></br>//<br></br>CREATE FUNCTION counter2 () RETURNS INT<br></br> BEGIN<br></br> UPDATE counter SET c = c + 2;<br></br> RETURN (SELECT c FROM counter LIMIT 1);<br></br> END;<br></br>//<br></br>DELIMITER ;
```

---

### Error while sending QUERY packet

Change your maxallowedpacket by using one of the following methods:

- In mysql prompt, enter `SET GLOBAL max_allowed_packet=524288000;`
- Set `max_allowed_packet` in `my.ini`