Category Archives: MySQL

Change Mysql (MariaDB) for table name lowercase

Stop Mysql service

  • run services.msc to open windows service manager
  • Stop MySQL service

Update my.ini (C:\Program Files\MariaDB 10.3\data\my.ini)

Add lower_case_table_names = 2

Start MySQL

  • Stop MySQL service


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)


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



Get members by birthday duration



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:
  7. Grant permission to an user:


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:



Get nearby places using SQL

Get result of stored procedure via PHP

DB account must have “execute” permission!

Stored procedure example:

PHP example: (Codeigniter)