iTranslated by AI
[DB Client] Effortlessly Manage MariaDB/MySQL in Your Terminal with mycli
Prerequisites / Target Audience
-
You have already installed MariaDB (or MySQL).
If you are a beginner and haven't installed it yet, please refer to the MariaDB installation section of this article I wrote. -
People who have a general understanding of SQL
It should be fine if you at least know terms likeSELECTandINSERT.
*If you want to study SQL and RDBs properly, I recommend starting with books like "Sukkiri Wakaru SQL Nyumon" (Refreshing Guide to SQL)! -
People who want to casually play around with databases in the CLI (that black terminal screen)
When it comes to working with large-scale databases for real, using a GUI like Sequel Pro is honestly easier.
However, for personal projects or quick experiments, mycli is casual and nice... I think. Plus, it serves as good training for using the terminal!
*For those unique individuals who want to read this article in English, please check out the article I posted on dev.to.
Preparation: Let's Set Up mycli
What exactly is mycli?
mycli is a "CLI interface for common database management systems like MySQL (and MariaDB)."
*mycli stands for MySQL client. CLI stands for Command Line Interface.
So, what's the benefit of using mycli?
The best part is that it provides auto-completion, making it easy to type commands in the terminal and reducing typos.
- Auto-completion for basic commands
- Suggestions for table names and more
- Customizable colors for better readability
The screen above shows a connection using the standard mysql command. It's hard to read and command completion doesn't work...
This screen shows a connection using the mycli command. It has colors, shows suggestions, and is wonderful...!
Installing mycli
You can install it using pip or homebrew.
-- If you're on a Mac, use the following:
$ brew update
$ brew install mycli
-- If you're a Python user, you can also use this:
$ pip install mycli
Please check if mycli was installed correctly.
$ mycli --version
Version: 1.21.1
Modifying mycli Settings such as Colors
Modify the configuration file ~/.myclirc by referring to the mycli official website.
# Color change (will look like a light green color.)
syntax_style = native
# Change key bindings to vi (if you don't know vim keybindings, you can leave it as emacs!)
key_bindings = vi
# Display a wider completion menu
wider_completion_menu = True
Playing Around with MariaDB (MySQL) using mycli
Connecting to the Database
If you haven't created a database yet or want to see what databases are available, try skipping the database name and follow the "Check Database List" section below.
$ mycli -uUsername -pPassword DatabaseName
-- Example
$ mycli -uroot -pHOGEHOGE fuga_db
Check Database List
> SHOW DATABASES;
Create Database
> CREATE DATABASE IF NOT EXISTS test;
Delete Database
> DROP DATABASE IF EXISTS test;
Check Connection Hostname
> SHOW VARIABLES LIKE 'hostname';
Create Table
CREATE TABLE Syntax / MySQL Official
By the way, we will create a table like this with the following query.
The relations look like this (this was quickly made using a tool called tbls).
CREATE TABLE TableName (ColumnName DataType Constraints,...
FOREIGN KEY IndexName (ColumnName, ...)
REFERENCES ReferenceTableName (ColumnName, ...),
INDEX IndexName(ColumnName) COMMENT 'comment');
-- Example
> CREATE TABLE IF NOT EXISTS members (
id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(255) NOT NULL UNIQUE,
email varchar(255) NOT NULL UNIQUE,
password varchar(100) NOT NULL,
picture varchar(255) NOT NULL DEFAULT '/default_icon.jpg',
created DATETIME NOT NULL,
modified TIMESTAMP NOT NULL,
-- Since queries to set up indexes/keys tend to be long, I separate them like this
INDEX idx_members_id_01(id) COMMENT 'for search member quickly'
);
> CREATE TABLE IF NOT EXISTS posts (
post_id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id MEDIUMINT NOT NULL,
content VARCHAR(900),
posted_at TIMESTAMP NOT NULL,
FOREIGN KEY fk_posts_userid_01(user_id)
REFERENCES members (id)
);
List Tables
> SHOW TABLES;
Check Table Structure
-- DESCRIBE is a synonym for EXPLAIN
> DESCRIBE TableName;
-- Example
> DESCRIBE members;
*Reference:
"Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT / MySQL Official"
Show CREATE TABLE Statement
SHOW CREATE TABLE Syntax
You can get more detailed information than the DESCRIBE statement.
> SHOW CREATE TABLE TableName;
Drop Table
> DROP TABLE IF EXISTS TableName
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Check Indexes
> SHOW INDEX FROM TableName;
-- Example
> SHOW INDEX FROM members;
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+
| members | 0 | PRIMARY | 1 | id | A | 0 | <null> | <null> | | BTREE | | |
| members | 0 | name | 1 | name | A | 0 | <null> | <null> | | BTREE | | |
| members | 0 | email | 1 | email | A | 0 | <null> | <null> | | BTREE | | |
| members | 1 | idx_members_id_01 | 1 | id | A | 0 | <null> | <null> | | BTREE | | for search member quickly |
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+
The meanings of the above columns are as follows:
| Item | Description |
|---|---|
| Table | The name of the table |
| Non_unique | 0 if the index cannot contain duplicates, 1 if it can. |
| Key_name | The name of the index. If the index is a primary key, the name is always PRIMARY |
| Seq_in_index | The column sequence number within the index, starting from 1. |
| Column_name | Column name |
| Collation | How the column is sorted. In MySQL, this can be "A" (ascending) or NULL (not sorted). |
| Cardinality | An estimate of the number of unique values in the index. Higher cardinality means a higher chance that MySQL will use this index during joins. |
| Sub_part | The number of indexed characters if the column is only partially indexed, or NULL if the entire column is indexed. |
| Packed | Indicates how the key is packed. NULL if it is not. |
| Null | Contains YES if the column may contain NULL values and '' if not. |
| Index_type | The index method used (BTREE, FULLTEXT, HASH, RTREE). |
| Comment | Information about the index (e.g., "disabled" if the index is disabled). |
| Index_comment | Any comment provided for the index when it was created with the COMMENT attribute. |
*Reference:
"SHOW INDEX Statement / MySQL Official"
"Setting INDEX on columns with low cardinality in MySQL(InnoDB) / Qiita Article"
By the way, regarding the B-tree indexing method, I recommend articles like "Introduction to B-tree indexes / Qiita Article" or "B-tree algorithm".
For now, you can proceed with the image that properly setting up indexes creates something like a book index, which speeds up searches (this explanation might get me scolded by experts, sweat).
Add Index to an Existing Table Column
> ALTER TABLE TableName ADD INDEX IndexName(ColumnName);
Delete Index
DROP INDEX Syntax / Official
ALTER TABLE Syntax / Official
> DROP INDEX IndexName ON TableName
-- Or
> ALTER TABLE TableName DROP INDEX IndexName;
Change Index Comment
> ALTER TABLE TableName
DROP INDEX IndexName,
ADD FULLTEXT INDEX IndexName (ColumnName) COMMENT "Comment";
-- Example
> ALTER TABLE members
DROP INDEX idx_members_id_01,
ADD INDEX idx_members_01 (id) COMMENT "HOGE";
Data Insertion (INSERT)
> INSERT INTO TableName (ColumnName) VALUES (Value);
-- Example 1 (No need to specify column names when inserting data for all columns, though you can if you want)
> INSERT INTO members
VALUES
(1, 'Oda Nobunaga', 'nobu_oda@example.com', 'HOGE', '/oda_icon.jpg', '2020-12-02 1:14:22', '2020-12-02 1:14:22');
-- Example 2 (When inserting data into specific columns)
> INSERT INTO members
(name, email, password, picture, created)
VALUES
('Toyotomi Hideyoshi', 'hide_toyo@example.com', 'FUGA', '/toyo_icon.jpg', '2020-12-01 10:00:00');
Data Row Deletion (DELETE)
> DELETE FROM TableName;
-- Example 1 (All rows will be deleted)
DELETE FROM members;
-- Example 2 (Use a WHERE clause to limit the deletion)
DELETE FROM members WHERE id=3;
Data Selection (SELECT)
> SELECT ColumnName FROM Table;
-- Example 1: Specific columns
> SELECT id, name FROM members;
-- Example 2: All columns
> SELECT * FROM members;
-- Example 3: Specific rows only
> SELECT * FROM members WHERE id=3;
Issues I ran into
- A column with the
AUTO_INCREMENTconstraint must always have a key defined.
Set an index or define it as a PRIMARY KEY.
CREATE TABLE members (
id INT NOT NULL AUTO_INCREMENT,
name varchar(255)
);
(1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key')
- Unable to DROP INDEX
Check the existence of the following indexes using SHOW INDEX.
Attempting to delete PRIMARY, but...
> ALTER TABLE members DROP INDEX PRIMARY;
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PRIMARY ON members' at line 1")
The cause is that while the primary key's index name is always PRIMARY, PRIMARY is a reserved word. Therefore, it results in an error if not specified as a quoted identifier. Enclosing it in backticks `` as shown below will work.
> ALTER TABLE members DROP INDEX `PRIMARY`;
Note that you cannot DROP a KEY on a column that has AUTO_INCREMENT set and is the only KEY.
Summary of Tidbits (to be updated as needed)
- Size of varchar type
[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
varchar is a variable-length string. (There is also the char type for fixed-length strings.) M represents the maximum column length in characters. You can set a range from 0 to 65,532... however, the effective maximum length depends on the maximum row size and the character set used.
Since a utf-8 character requires up to 3 bytes per character, a VARCHAR column using the utf-8 character set can be declared with a maximum of 21,844 characters.
*Reference:
Discussion