Category Archives: MySQL

Mysql (MariaDB) force table name to lower case when create table

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

Reference:

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:

    1. ‘user’@’localhost’ 後面的localhost是指可以存取DB的IP,不是DB server的IP
    2. yourpassword不需要填加密的,直接用明碼就可以了
    3. ‘10.0.1.%’ could be replaced with ‘localhost’
      Password generator: https://www.browserling.com/tools/mysql-password

Creating an user with adminer:

  • 如果要讓user透過adminer登入,目前測試server欄位是一定要填%

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