Category Archives: MySQL

SQL 錯誤 (1071):Specified key was too long; max key length is 767 bytes

Key too long issue

utf8 時,會使用 255 * 3 = 765 (bytes)
utf8mb4 255 * 4 = 1020 (so limit length is 191)

Replace varchar(200) with varchar(191)

Reference:

Having and group by

 

Save emoji in MySQL

Change charset to “utf8mb4” and collation to “utf8mb4_unicode_ci”

emoji example:

Hdhdhdh????hzhzhzzhjzj 我爱你 ❌

  • DB table
    Set COLLATE ‘utf8mb4_unicode_ci’
  • DB connection

Reference:

  1. http://www.hangge.com/blog/cache/detail_1411.html
  2. https://stackoverflow.com/questions/39463134/how-to-store-emoji-character-in-my-sql-database

Get members by birthday duration

PHP

 

MySQL Commands

  1. Login:
  2. Show password policy:
  3. Show all databases
  4. Backup DB:
  5. Import DB:
  6. Create a new user:

    Note: ‘10.0.1.%’ could be replaced with ‘localhost’
    Password generator: https://www.browserling.com/tools/mysql-password

Referece:

  1. https://www.opencli.com/mysql/mysql-add-new-users-databases-privileges

Join a sub query with group by statement

Find members that have the same password

More Advanced – Get minimum member ID from members that have same password:

 

Reference:

Get nearby places using SQL

Get result of stored procedure via PHP

DB account must have “execute” permission!

Stored procedure example:

PHP example: (Codeigniter)

Reference:

Using binary column in where clause