MySQL AI Chatbot: Setup, SQL Examples and Validation

A MySQL AI chatbot turns questions about your database into MySQL queries and presents the results in a conversation. To make it useful, connect a restricted database account, explain your business metrics, and verify the generated SQL against answers you already know. AskYourDatabase provides this workflow through Desktop for internal analysis and Website Chatbot for customer-facing use.

This guide covers connecting an existing MySQL database and checking a concrete sales-and-refunds example. It is published by AskYourDatabase and includes our product workflow. The SQL fixture below is synthetic and independent of the product; it is not a customer result or an AI accuracy benchmark.

Choose the MySQL workflow you need

A search for “MySQL AI” can also mean Oracle's MySQL AI and HeatWave features, rather than a chatbot connected to an existing server. Google Cloud also documents QueryData for Cloud SQL for MySQL. Check the service and deployment requirements of those offerings; they are not automatically available on every MySQL installation.

For an interactive database client, see the MySQL GUI page. For MySQL SQL generation, see the query generator workflow and checked NULL/date/JSON examples. The examples can be copied independently; connected generation happens in the app. This guide focuses on setup and answer validation. The broader natural-language query guide explains the concepts across engines.

Connect MySQL to AskYourDatabase

1. Prepare a limited reporting account

Ask the database owner for the hostname, port, database name and an account scoped to the tables or reporting views the chatbot should read. Prefer a reporting replica or dedicated reporting dataset where appropriate. Start with a small scope instead of sharing an administrator account.

The owner should grant only the privileges required for the intended reads and metadata discovery. MySQL's GRANT documentation describes database, table and column privilege scopes. Verify the effective permissions with that account; do not assume a prompt saying “read only” blocks writes. Read permission also does not make an expensive query cheap.

Have the owner document which columns are sensitive, which rows are allowed and what query resource limits apply. A sample database or the isolated fixture below is a useful first connection before accessing business data.

2. Check where the connection originates

In Desktop, MySQL must be reachable from your computer, including any required VPN or approved network route. Website Chatbot uses a server-side connection. Follow the current connection instructions for that environment's network allowlist.

A hosted chatbot cannot reach a MySQL instance on your laptop by using localhost: that name refers to the machine making the connection. Do not solve this by broadly exposing the database to the internet. Agree on an approved network path with its owner.

Use the TLS configuration required by your database provider and confirm certificate verification for your chosen client and deployment. An encrypted connection and verified server identity are separate properties; neither should be inferred solely from a successful connection.

3. Add the database in Desktop

Download AskYourDatabase Desktop, open the database connection flow, choose MySQL, and provide the connection details. The connection-string shape is:

mysql://USER:PASSWORD@HOST:3306/DATABASE

These are placeholders, not working credentials. Keep real credentials in the connection configuration rather than chat messages or shared examples. If a credential contains URI delimiters, follow the connection form's encoding requirements instead of pasting an ambiguous string.

The image shows the product's database-selection workflow. Confirm that the expected database and reporting tables are available, then start with a small known question such as an approved row count. A successful connection proves connectivity; it does not prove that the chatbot understands revenue or customer eligibility.

4. Teach the metric before asking for a chart

Add concise business documentation and reviewed question/SQL pairs using training documentation and examples. For example: “Paid sales excludes cancelled orders, is grouped by order date in UTC, and is reported separately by currency. Refunds must be summed per order before joining.”

Check the SQL and underlying numbers before requesting a chart or a follow-up. Preserve the metric definition across the conversation. If “sales” could mean orders placed, cash collected or recognized revenue, ask the stakeholder to choose the definition first.

A reproducible MySQL chatbot example: sales after refunds

The common failure in this example is a one-to-many join. One order has two refunds. A direct join repeats its gross amount twice, so a query can run successfully and still overstate sales.

Question to validate: “For paid USD orders placed in August 2026, show gross sales, refunds recorded before September 1, and net sales by order month. Treat the stored dates as UTC.”

The contract is deliberately narrow: one currency; paid order status; August order dates; refunds through the stated cutoff; net sales equals gross minus those refunds. This is an order-cohort report, not an accounting revenue-recognition or cash-flow report. The fixture stores only the current order status, so it does not reconstruct historical status changes.

Create the sample tables in an isolated test database

Use an empty local test schema with no production connection. A database owner should create these synthetic tables; the chatbot's reporting account should remain restricted to reading them. The DATETIME values below represent UTC by convention. MySQL does not attach that convention to the column for you; see its DATETIME and TIMESTAMP behavior.

CREATE TABLE demo_orders (
  order_id INT PRIMARY KEY,
  placed_at DATETIME NOT NULL,
  status VARCHAR(20) NOT NULL,
  currency CHAR(3) NOT NULL,
  gross_amount DECIMAL(10,2) NOT NULL
);
CREATE TABLE demo_refunds (
  refund_id INT PRIMARY KEY,
  order_id INT NOT NULL,
  refunded_at DATETIME NOT NULL,
  amount DECIMAL(10,2) NOT NULL
);
INSERT INTO demo_orders VALUES
  (1, '2026-08-02 10:00:00', 'paid', 'USD', 100.00),
  (2, '2026-08-03 10:00:00', 'paid', 'USD', 80.00),
  (3, '2026-08-04 10:00:00', 'cancelled', 'USD', 50.00),
  (4, '2026-09-01 00:00:00', 'paid', 'USD', 200.00),
  (5, '2026-08-05 10:00:00', 'paid', 'EUR', 90.00);
INSERT INTO demo_refunds VALUES
  (11, 1, '2026-08-10 09:00:00', 20.00),
  (12, 1, '2026-08-11 09:00:00', 10.00),
  (13, 2, '2026-09-02 09:00:00', 15.00);

Compare the generated answer with this reference query

The derived table reduces refunds to one row per order before the join. COALESCE treats an order without a qualifying refund as zero refunded. The half-open date range excludes the order exactly at September 1 midnight.

SELECT
  DATE_FORMAT(o.placed_at, '%Y-%m') AS order_month,
  o.currency,
  COUNT(*) AS paid_orders,
  SUM(o.gross_amount) AS gross_sales,
  SUM(COALESCE(r.refunded_amount, 0)) AS refunds_to_cutoff,
  SUM(o.gross_amount - COALESCE(r.refunded_amount, 0)) AS net_sales
FROM demo_orders AS o
LEFT JOIN (
  SELECT order_id, SUM(amount) AS refunded_amount
  FROM demo_refunds
  WHERE refunded_at < '2026-09-01 00:00:00'
  GROUP BY order_id
) AS r ON r.order_id = o.order_id
WHERE o.status = 'paid'
  AND o.currency = 'USD'
  AND o.placed_at >= '2026-08-01 00:00:00'
  AND o.placed_at < '2026-09-01 00:00:00'
GROUP BY DATE_FORMAT(o.placed_at, '%Y-%m'), o.currency
ORDER BY order_month, o.currency;

Expected output:

Reconcile it manually: orders 1 and 2 contribute 100 + 80. The two August refunds contribute 20 + 10, so net sales is 150. The cancelled order, September order and EUR order are excluded. Order 2's September refund is beyond the cutoff.

The reference SQL was executed against this fixture in an isolated local MySQL 5.7.24 test instance with ONLY_FULL_GROUP_BY enabled. This records the available test environment, not a recommendation to deploy that old server version or proof of an AskYourDatabase-generated answer. The query uses a derived table rather than a version-specific CTE. Validate it on your supported MySQL version and actual schema before reuse.

MySQL's DATE_FORMAT reference explains month formatting. The GROUP BY rules explain why nonaggregated selected expressions need appropriate grouping. Fix an ambiguous query rather than disabling SQL mode to silence the error.

Test follow-up questions and failure cases

Only the numeric fixture cases demonstrate SQL arithmetic. They do not demonstrate tenant isolation, product accuracy across schemas or production performance. Record each prompt, approved SQL, expected result and actual result when evaluating your chatbot.

MySQL connection and answer troubleshooting

For a large schema, begin with a focused business domain and approved examples. Adding more tables or training text does not establish a universal accuracy guarantee. Our practical AI-querying guide covers the wider workflow, while the ERP reporting guide shows a different reconciliation problem involving receivables and as-of payments.

From an internal MySQL chatbot to a customer-facing product

Desktop is a starting point for a person querying a database they are authorized to use. A customer-facing chatbot needs an application authorization boundary as well as database privileges. Read-only access can still expose every readable customer's data if no narrower scope is enforced.

Use the embedded chatbot implementation guide for backend session creation, authenticated identity and isolation checks. Review access control, test two distinct customer identities, and verify that changing a question or browser-supplied value cannot widen the allowed data. Embedding a chat interface alone does not create tenant isolation.

Connection location also differs from AI processing location. Standard Desktop SQL execution happens locally, but cloud AI services can process relevant schema, context and result data. Review the security documentation; for private infrastructure requirements, discuss the model endpoint, storage, logs and network boundaries using the on-premises guide.

Choose the next step that matches the workflow: Desktop download for internal analysis, embedded analytics chatbot for a customer portal, or current plans to compare Desktop, Website Chatbot and enterprise options. Start with one validated metric and expand only after the access and answer checks pass.

Frequently asked questions

How do I connect an AI chatbot to MySQL?

Prepare a MySQL account limited to the reporting data, make the database reachable from the connection environment, and add the connection in AskYourDatabase. Supply metric definitions and validate a known question before expanding access.

Can I chat with MySQL without writing SQL?

Yes. AskYourDatabase lets you ask questions in natural language and receive SQL-based results. A database owner still needs to configure access and verify important calculations; a fluent answer is not proof of correctness.

Can the Desktop app connect to a local MySQL database?

Desktop connects from your computer, so the MySQL server must be reachable from that computer. A hosted Website Chatbot connects from its server environment; localhost there does not refer to your laptop.

Why does a MySQL chatbot double-count sales?

Joining orders directly to multiple refund or line-item rows can repeat each order amount. Aggregate the child rows to one row per order before joining, then verify totals against a small known dataset.

Does a read-only MySQL account isolate customers?

No. Read-only permissions restrict writes but do not automatically restrict which customer rows can be read. Customer-facing chatbots need separately enforced authorization and data scoping, plus tests for cross-customer access.

Does running AskYourDatabase Desktop mean offline AI?

No. Desktop connects to MySQL and executes SQL locally, but the standard AI workflow uses cloud services. Relevant context and result data may be processed externally. Confirm model, storage and network boundaries for private deployment requirements.