[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> DROP TABLE IF EXISTS T_TEST;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE T_TEST(
    ->   IDX INT NOT NULL
    ->   ,NM VARCHAR(10) NOT NULL
    ->   ,V_SORT CHAR(1) AS (CASE
    ->     WHEN (SUBSTR(NM,1,1) REGEXP '^[ㄱ-힣]' )=1 THEN '1'
    ->     WHEN (SUBSTR(NM,1,1) REGEXP '^[A-Za-z]')=1 THEN '2'
    ->     WHEN (SUBSTR(NM,1,1) REGEXP '^[0-9]'   )=1 THEN '3'
    ->     ELSE '4' END) VIRTUAL
    ->   )CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>  ALTER TABLE T_TEST ADD PRIMARY KEY (NM);
  ALTER TABLE T_TEST  ADD KEY (IDX, V_SORT);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>   ALTER TABLE T_TEST  ADD KEY (IDX, V_SORT);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO T_TEST (IDX, NM) VALUES (1,'대한민국');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO T_TEST (IDX, NM) VALUES (2,'ΛBCD');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO T_TEST (IDX, NM) VALUES (3,'ABBB');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO T_TEST (IDX, NM) VALUES (4,'123456');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM T_TEST;
+-----+--------------+--------+
| IDX | NM           | V_SORT |
+-----+--------------+--------+
|   1 | 대한민국     | 1      |
|   2 | ΛBCD         | 1      |
|   3 | ABBB         | 2      |
|   4 | 123456       | 3      |
+-----+--------------+--------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE T_TEST
    ->   MODIFY V_SORT CHAR(1) AS (CASE
    ->     WHEN (SUBSTR(NM,1,1) REGEXP '^[ㄱ-힣]' )=1 AND LENGTH(SUBSTR(NM,1,1))=3 THEN '1'
    ->     WHEN (SUBSTR(NM,1,1) REGEXP '^[A-Za-z]')=1 THEN '2'
    ->     WHEN (SUBSTR(NM,1,1) REGEXP '^[0-9]'   )=1 THEN '3'
    ->     ELSE '4' END)  ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM T_TEST;
+-----+--------------+--------+
| IDX | NM           | V_SORT |
+-----+--------------+--------+
|   1 | 대한민국     | 1      |
|   2 | ΛBCD         | 1      |
|   3 | ABBB         | 2      |
|   4 | 123456       | 3      |
+-----+--------------+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM T_TEST WHERE NM='ΛBCD';
+-----+-------+--------+
| IDX | NM    | V_SORT |
+-----+-------+--------+
|   2 | ΛBCD  | 4      |
+-----+-------+--------+
1 row in set (0.00 sec)

mysql> SELECT NM, IDX, V_SORT FROM T_TEST USE INDEX(IDX) WHERE NM='ΛBCD';
+-------+-----+--------+
| NM    | IDX | V_SORT |
+-------+-----+--------+
| ΛBCD  |   2 | 1      |
+-------+-----+--------+
1 row in set (0.00 sec)

mysql> UPDATE T_TEST
    -> SET NM='가나다'
    -> WHERE NM='ΛBCD';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM T_TEST;
+-----+--------------+--------+
| IDX | NM           | V_SORT |
+-----+--------------+--------+
|   1 | 대한민국     | 1      |
|   2 | ΛBCD         | 1      |
|   2 | 가나다       | 1      |
|   3 | ABBB         | 2      |
|   4 | 123456       | 3      |
+-----+--------------+--------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM T_TEST WHERE NM='ΛBCD';
Empty set (0.00 sec)

mysql> SELECT NM, IDX, V_SORT FROM T_TEST USE INDEX(IDX) WHERE NM='ΛBCD';
+-------+-----+--------+
| NM    | IDX | V_SORT |
+-------+-----+--------+
| ΛBCD  |   2 | 1      |
+-------+-----+--------+
1 row in set (0.00 sec)

mysql> show create table T_TEST\G
*************************** 1. row ***************************
       Table: T_TEST
Create Table: CREATE TABLE `T_TEST` (
  `IDX` int(11) NOT NULL,
  `NM` varchar(10) NOT NULL,
  `V_SORT` char(1) GENERATED ALWAYS AS (CASE
    WHEN (SUBSTR(NM,1,1) REGEXP '^[ㄱ-힣]' )=1 AND LENGTH(SUBSTR(NM,1,1))=3 THEN '1'
    WHEN (SUBSTR(NM,1,1) REGEXP '^[A-Za-z]')=1 THEN '2'
    WHEN (SUBSTR(NM,1,1) REGEXP '^[0-9]'   )=1 THEN '3'
    ELSE '4' END) VIRTUAL,
  PRIMARY KEY (`NM`),
  KEY `IDX` (`IDX`,`V_SORT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)