[openxs@fc23 maria10.2]$ bin/mysql -uroot --socket=/tmp/mariadb.sock test
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 8
|
Server version: 10.2.17-MariaDB Source distribution
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [test]> create table tv(id int primary key, c1 varchar(10), c2 varchar(10) as (upper(c1)) virtual);
|
Query OK, 0 rows affected (0.15 sec)
|
|
MariaDB [test]> insert into tv(id, c1) values (1, 'abc');
|
Query OK, 1 row affected (0.04 sec)
|
|
MariaDB [test]> select * from tv;
|
+----+------+------+
|
| id | c1 | c2 |
|
+----+------+------+
|
| 1 | abc | ABC |
|
+----+------+------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> alter table tv add key(c2);
|
Query OK, 0 rows affected (0.27 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> show create table tv\G
|
*************************** 1. row ***************************
|
Table: tv
|
Create Table: CREATE TABLE `tv` (
|
`id` int(11) NOT NULL,
|
`c1` varchar(10) DEFAULT NULL,
|
`c2` varchar(10) GENERATED ALWAYS AS (ucase(`c1`)) VIRTUAL,
|
PRIMARY KEY (`id`),
|
KEY `c2` (`c2`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> explain select c2 from tv where c2 = 'ABC';
|
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|
| 1 | SIMPLE | tv | ref | c2 | c2 | 13 | const | 1 | Using where; Using index |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|
1 row in set (0.00 sec)
|