AnalyticDB-MySQL CLI

Installation

• Install from PyPI

  uv tool install analyticdb-mysql-cli

• Install from local source code

  cd analyticdb-mysql-cli
  uv tool install .

After installation, you can use the adbmysql-cli command, or use analyticdb-mysql-cli as an alias for the same program.

Connection configuration

You can configure the connection by using the --dsn command-line option, the ADBMYSQL_DSN environment variable, or a .env file in your project. These options are listed in descending order of priority.

To create a global configuration file to connect to an AnalyticDB for MySQL cluster, run the following command:

mkdir -p ~/.adbmysql
echo 'ADBMYSQL_DSN="mysql://user:pass@host:port/database"' > ~/.adbmysql/config.env

Structured JSON output

All analyticdb-mysql-cli commands return a standardized JSON structure:

// Success response
{
  "ok": true,
  "data": "...",
  "time_ms": 123
}

// Error response
{
  "ok": false,
  "error": {
    "code": "SQL_ERROR",
    "message": "Detailed error message"
  }
}

An AI agent can check the ok field to determine the result, without complex text parsing.

Example of listing tables:

analyticdb-mysql-cli schema tables

Example response:

{
  "ok": true,
  "data": [
    {"name": "e_categories", "columns": 3, "rows": 15},
    {"name": "e_orders", "columns": 6, "rows": 800},
    {"name": "e_order_items", "columns": 5, "rows": 1080},
    {"name": "e_products", "columns": 6, "rows": 50},
    {"name": "e_users", "columns": 6, "rows": 80}
  ],
  "time_ms": 261
}

An AI agent can parse the result with JSON.parse() to obtain table names, column counts, and row counts.

Schema exploration

analyticdb-mysql-cli provides a complete set of schema exploration commands to help AI agents understand database structure.

View table structure:

analyticdb-mysql-cli schema describe e_orders

Returns detailed information about the table's structure, including columns, types, and indexes. Example response:

Export DDL:

analyticdb-mysql-cli schema dump

Outputs the CREATE TABLE statements for all tables, helping AI agents understand the complete database design.

Data profiling (table profile)

The table profile command analyzes the statistical characteristics of a table:

analyticdb-mysql-cli table profile e_orders

The result includes:

• Row count

• Null value ratio

• Number of distinct values

• Value range

• Most frequent values

• Candidate join keys

• Time columns

An AI agent can use this information to decide which fields to group by or which enumerated values to handle.

Relation inference (relations infer)

analyticdb-mysql-cli can automatically discover relationships between tables:

analyticdb-mysql-cli relations infer

Example response:

{
  "data": [
    {"from": "e_order_items.order_id", "to": "e_orders.order_id", "confidence": "medium"},
    {"from": "e_order_items.product_id", "to": "e_products.product_id", "confidence": "medium"},
    {"from": "e_orders.user_id", "to": "e_users.user_id", "confidence": "medium"}
  ]
}

This allows an AI agent to understand JOIN relationships without consulting documentation or foreign key constraints, which simplifies generating multi-table JOIN queries. For example, from the response above, an AI agent can determine that:

• The e_order_items table joins to the e_orders table on order_id.

• The e_order_items table joins to the e_products table on product_id.

• The e_orders table joins to the e_users table on user_id.

Multiple output formats

analyticdb-mysql-cli supports multiple output formats:

# Table format (human-readable)
analyticdb-mysql-cli --format table sql "SELECT * FROM e_users LIMIT 3"

# CSV format (for data export)
analyticdb-mysql-cli --format csv sql "SELECT * FROM e_users LIMIT 3"

# JSONL format (for stream processing)
analyticdb-mysql-cli --format jsonl sql "SELECT * FROM e_users LIMIT 3"

Example of table format output:

user_id | username | city | province | register_date | user_level
--------+----------+------+----------+---------------+-----------
     46 | Li Min   | Chengdu | Sichuan  | 2024-05-02    | Silver
     60 | Chen Tao | Nanchang | Jiangxi  | 2023-12-28    | Silver
     57 | Li Tao   | Xiamen | Fujian   | 2023-06-12    | Diamond

Row count protection

When a query does not include a LIMIT clause, the tool automatically checks the result set size. If it exceeds 1,001 rows, a LIMIT_REQUIRED error is returned to prevent memory overflow or network congestion from fetching large datasets.

Write operation safeguards

By default, all SQL statements are executed in read-only mode. To perform write operations such as INSERT, UPDATE, or DELETE, you must explicitly add the --write parameter. DELETE or UPDATE statements without a WHERE clause are blocked to prevent accidentally deleting an entire table.

Sensitive data masking

The tool automatically masks sensitive fields such as phone, email, password, and id_card to prevent data exposure.

Test tables and data

• Users table: e_users

  CREATE TABLE `e_users` (
    `user_id` int NOT NULL,
    `username` varchar(100) NOT NULL,
    `city` varchar(50) NOT NULL,
    `province` varchar(50) NOT NULL,
    `register_date` date NOT NULL,
    `user_level` varchar(20) NOT NULL,
    KEY `pk_sys_user_id` (`user_id`),
    PRIMARY KEY (`user_id`)
  ) DISTRIBUTE BY HASH(`user_id`) STORAGE_POLICY='HOT' ENGINE='XUANWU_V2';

• Product categories table: e_categories

  CREATE TABLE `e_categories` (
    `category_id` int NOT NULL,
    `category_name` varchar(100) NOT NULL,
    `parent_category_id` int,
    KEY `pk_sys_category_id` (`category_id`),
    PRIMARY KEY (`category_id`)
  ) DISTRIBUTE BY HASH(`category_id`) STORAGE_POLICY='HOT' ENGINE='XUANWU_V2';

• Products table: e_products

  CREATE TABLE `e_products` (
    `product_id` int NOT NULL,
    `product_name` varchar(200) NOT NULL,
    `category_id` int NOT NULL,
    `price` decimal(10, 2) NOT NULL,
    `cost` decimal(10, 2) NOT NULL,
    `created_at` date NOT NULL,
    KEY `pk_sys_product_id` (`product_id`),
    PRIMARY KEY (`product_id`)
  ) DISTRIBUTE BY HASH(`product_id`) STORAGE_POLICY='HOT' ENGINE='XUANWU_V2';

• Orders table: e_orders

  CREATE TABLE `e_orders` (
    `order_id` bigint NOT NULL,
    `user_id` int NOT NULL,
    `order_date` date NOT NULL,
    `total_amount` decimal(12, 2) NOT NULL,
    `status` varchar(20) NOT NULL,
    `payment_method` varchar(30) NOT NULL,
    KEY `pk_sys_order_date` (`order_date`),
    KEY `pk_sys_order_id` (`order_id`),
    PRIMARY KEY (`order_id`,`order_date`)
  ) DISTRIBUTE BY HASH(`order_id`) PARTITION BY VALUE(`DATE_FORMAT(order_date, '%Y')`) STORAGE_POLICY='HOT' ENGINE='XUANWU_V2';

• Order items table: e_order_items

  CREATE TABLE `e_order_items` (
    `item_id` bigint NOT NULL,
    `order_id` bigint NOT NULL,
    `product_id` int NOT NULL,
    `quantity` int NOT NULL,
    `unit_price` decimal(10, 2) NOT NULL,
    KEY `pk_sys_item_id` (`item_id`),
    KEY `pk_sys_order_id` (`order_id`),
    PRIMARY KEY (`order_id`,`item_id`)
  ) DISTRIBUTE BY HASH(`order_id`) STORAGE_POLICY='HOT' ENGINE='XUANWU_V2';

For test data, see ecommerce_demo.sql.

AI agent analysis workflow

In a scenario where you want to analyze sales data, the AI agent follows this workflow:

1. Get a table overview

   analyticdb-mysql-cli schema tables
   # Discovers five tables, including e_orders, e_order_items, and e_products.

2. View the structure and profile of the orders table

   analyticdb-mysql-cli table profile e_orders
   # Reveals that there are 800 orders and the status column has four distinct values.

3. Infer table relationships

   analyticdb-mysql-cli relations infer
   # Discovers the relationships between orders, users, and products.

4. Execute multi-dimensional analysis queries

   Based on the retrieved metadata, the AI agent can run the following analysis queries.

   • Overview

     analyticdb-mysql-cli sql "SELECT COUNT(*) as total_orders, SUM(total_amount) as total_revenue, AVG(total_amount) as avg_order_value, MIN(order_date) as first_order, MAX(order_date) as last_order FROM e_orders"

   • Order status distribution

     analyticdb-mysql-cli sql "SELECT status, COUNT(*) as count, SUM(total_amount) as amount FROM e_orders GROUP BY status ORDER BY count DESC"

   • Payment method analysis

     analyticdb-mysql-cli sql "SELECT payment_method, COUNT(*) as count, SUM(total_amount) as amount FROM e_orders GROUP BY payment_method ORDER BY amount DESC"

   • Monthly sales trend

     analyticdb-mysql-cli sql "SELECT DATE_FORMAT(order_date, '%Y-%m') as month, COUNT(*) as orders, SUM(total_amount) as revenue FROM e_orders WHERE status='completed' GROUP BY month ORDER BY month DESC LIMIT 12"

   • Top 10 best-selling products

     analyticdb-mysql-cli sql "SELECT p.product_name, SUM(oi.quantity) as sold_qty, SUM(oi.quantity * oi.unit_price) as revenue FROM e_order_items oi JOIN e_products p ON oi.product_id = p.product_id GROUP BY p.product_id, p.product_name ORDER BY revenue DESC LIMIT 10"

Sales analysis report

Based on the query results, the AI agent generates the following analysis report:

Throughout this process, the AI agent uses structured metadata to autonomously perform the entire analysis—from understanding data structure to generating a report—without manual intervention.