<!-- BusyBot node reference — https://busybot.net/tools/mysql/ -->

> Node: MySQL (`mysql`) · Action · v1
> Category: Data & Storage · Credentials: MySQL (`mysqlApi`)
> Updated: 2026-08-16

# MySQL

> Execute SQL queries and CRUD operations on a MySQL database.

## Overview

Connects to a MySQL server and performs structured CRUD operations — select, insert, update, upsert and delete — as well as raw SQL execution. Queries are parameterized to prevent SQL injection, and every identifier you supply is backtick-escaped. Data can be auto-mapped from the incoming item's fields to table columns or mapped column by column. WHERE clauses, sorting, limits, `INSERT IGNORE`, `ON DUPLICATE KEY UPDATE` and three query-batching modes (single, independent, transaction) are all supported.

**Category:** Data & Storage  
**Tool Name:** `mysql`  
**Version:** 1

**Appearance:** Icon: `si-mysql` | Color: `#4479A1`

## Node Type

**Action** — processes input items and produces output

## Input / Output

| Direction | Port(s) |
|-----------|--------|
| Input | `Input` |
| Output | `Output`, `Error` |

## Credentials

This tool requires **MySQL** credentials.
See the [Credentials Guide](https://busybot.net/credentials/mysql-api/) for setup instructions.

### Operations

| Operation | Value | Description |
|-----------|-------|-------------|
| Delete | `deleteTable` | Delete an entire table or rows in a table |
| Execute SQL | `executeQuery` | Execute an SQL query |
| Insert | `insert` | Insert rows in a table |
| Insert or Update | `upsert` | Insert or update rows in a table (ON DUPLICATE KEY UPDATE) |
| Select | `select` | Select rows from a table |
| Update | `update` | Update rows in a table |

### Parameters

#### Delete (`deleteTable`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Table | `string` | Yes | — | Name of the MySQL table to operate on. |
| Command | `options` | No | `truncate` | The delete command to execute. |
| | | | | Options: `truncate` (remove all rows but preserve table structure), `delete` (delete rows matching conditions; all rows if no conditions), `drop` (delete the table and its structure permanently) |
| Select Rows | `fixedCollection` | No | `{}` | WHERE conditions to filter rows. If not set, all rows are selected. |
| — Column | `string` | No | — | Column name to filter on. |
| — Operator | `options` | No | `equal` | Comparison operator. LIKE uses % for wildcards. |
| | | | | Options: `equal`, `!=`, `LIKE`, `>`, `<`, `>=`, `<=`, `IS NULL`, `IS NOT NULL` |
| — Value | `string` | No | — | Value to compare against. Not needed for IS NULL / IS NOT NULL. _(hidden when Operator is `IS NULL`, `IS NOT NULL`)_ |
| Combine Conditions | `options` | No | `AND` | How to combine multiple WHERE conditions. |
| | | | | Options: `AND` (all conditions must be true), `OR` (at least one condition must be true) |

#### Execute SQL (`executeQuery`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Query | `string` | Yes | — | The SQL query to execute. Use $1, $2, etc. for parameterized values and $1:name for identifier parameters. Set replacement values in Options > Query Parameters. |

#### Insert (`insert`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Table | `string` | Yes | — | Name of the MySQL table to operate on. |
| Data Mode | `options` | No | `autoMapInputData` | Whether to map input data to columns automatically or define columns manually. |
| | | | | Options: `autoMapInputData` (use when input field names match table column names exactly), `defineBelow` (set the value for each column manually) |
| Values to Send | `fixedCollection` | No | `{}` | Column-value pairs to insert or update. Used in manual data mode. _(shown when Data Mode is `defineBelow`)_ |
| — Column | `string` | No | — | Column name. |
| — Value | `string` | No | — | Value to set. |

#### Insert or Update (`upsert`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Table | `string` | Yes | — | Name of the MySQL table to operate on. |
| Data Mode | `options` | No | `autoMapInputData` | Whether to map input data to columns automatically or define columns manually. |
| | | | | Options: `autoMapInputData` (use when input field names match table column names exactly), `defineBelow` (set the value for each column manually) |
| Column to Match On | `string` | Yes | — | Column used in the WHERE clause to find the row to update. Must be a unique key for upsert. Enter the column name directly. |
| Value of Column to Match On | `string` | No | — | The value to match in the specified column. Used only in manual data mode. _(shown when Data Mode is `defineBelow`)_ |
| Values to Send | `fixedCollection` | No | `{}` | Column-value pairs to insert or update. Used in manual data mode. _(shown when Data Mode is `defineBelow`)_ |
| — Column | `string` | No | — | Column name. |
| — Value | `string` | No | — | Value to set. |

#### Select (`select`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Table | `string` | Yes | — | Name of the MySQL table to operate on. |
| Return All | `boolean` | No | `false` | Whether to return all results or only up to a given limit. |
| Limit | `number` | No | `50` | Max number of results to return. _(shown when Return All is `false`)_ |
| Select Rows | `fixedCollection` | No | `{}` | WHERE conditions to filter rows. If not set, all rows are selected. |
| — Column | `string` | No | — | Column name to filter on. |
| — Operator | `options` | No | `equal` | Comparison operator. LIKE uses % for wildcards. |
| | | | | Options: `equal`, `!=`, `LIKE`, `>`, `<`, `>=`, `<=`, `IS NULL`, `IS NOT NULL` |
| — Value | `string` | No | — | Value to compare against. Not needed for IS NULL / IS NOT NULL. _(hidden when Operator is `IS NULL`, `IS NOT NULL`)_ |
| Combine Conditions | `options` | No | `AND` | How to combine multiple WHERE conditions. |
| | | | | Options: `AND` (all conditions must be true), `OR` (at least one condition must be true) |
| Sort | `fixedCollection` | No | `{}` | ORDER BY rules for select queries. |
| — Column | `string` | No | — | Column to sort by. |
| — Direction | `options` | No | `ASC` | Sort direction. |
| | | | | Options: `ASC`, `DESC` |

#### Update (`update`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Table | `string` | Yes | — | Name of the MySQL table to operate on. |
| Data Mode | `options` | No | `autoMapInputData` | Whether to map input data to columns automatically or define columns manually. |
| | | | | Options: `autoMapInputData` (use when input field names match table column names exactly), `defineBelow` (set the value for each column manually) |
| Column to Match On | `string` | Yes | — | Column used in the WHERE clause to find the row to update. Must be a unique key for upsert. Enter the column name directly. |
| Value of Column to Match On | `string` | No | — | The value to match in the specified column. Used only in manual data mode. _(shown when Data Mode is `defineBelow`)_ |
| Values to Send | `fixedCollection` | No | `{}` | Column-value pairs to insert or update. Used in manual data mode. _(shown when Data Mode is `defineBelow`)_ |
| — Column | `string` | No | — | Column name. |
| — Value | `string` | No | — | Value to set. |

#### All Operations

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Options | `collection` | No | `{}` | Advanced connection and query settings. |
| — Connection Timeout (ms) | `number` | No | `30000` | Milliseconds reserved for connecting to the database. |
| — Connection Limit | `number` | No | `10` | Maximum number of connections. High values can cause performance issues. |
| — Query Batching | `options` | No | `single` | How queries should be batched and sent to the database. |
| | | | | Options: `single` (combine all items into a single query), `independently` (execute one query per input item), `transaction` (execute all queries in a transaction; rollback on failure) |
| — Query Parameters | `string` | No | — | Comma-separated values for $1, $2, $3 placeholders in your query. _(shown when Operation is `executeQuery`)_ |
| — Output Columns | `string` | No | `*` | Comma-separated column names to return, or * for all columns. _(shown when Operation is `select`)_ |
| — Output Large Numbers As | `options` | No | `text` | How to output NUMERIC and BIGINT columns. _(shown when Operation is `select` or `executeQuery`)_ |
| | | | | Options: `numbers`, `text` (use for numbers longer than 16 digits to avoid precision loss) |
| — Output Decimals as Numbers | `boolean` | No | `false` | Whether to output DECIMAL types as numbers instead of strings. _(shown when Operation is `select` or `executeQuery`)_ |
| — Priority | `options` | No | `LOW_PRIORITY` | Insert priority level. _(shown when Operation is `insert`)_ |
| | | | | Options: `LOW_PRIORITY` (delays execution until no other clients are reading from the table), `HIGH_PRIORITY` (overrides the --low-priority-updates option) |
| — Replace Empty Strings with NULL | `boolean` | No | `false` | Whether to replace empty strings with NULL in input data. _(shown when Operation is `insert`, `update`, `upsert` or `executeQuery`)_ |
| — Select Distinct | `boolean` | No | `false` | Whether to remove duplicate rows from results. _(shown when Operation is `select`)_ |
| — Output Query Execution Details | `boolean` | No | `false` | Whether to include the executed SQL in the output. |
| — Skip on Conflict | `boolean` | No | `false` | Whether to use INSERT IGNORE to skip rows that violate unique constraints. _(shown when Operation is `insert`)_ |
| Max Concurrency | `number` | No | `1` | Maximum number of items to process concurrently. Keep low for database connections. |

## Output Data

The result **replaces** the item's JSON — the incoming fields do not pass through — so downstream nodes see the database's answer and nothing else.

| Operation | Output |
|-----------|--------|
| `select` | **One item per returned row**, each carrying that row's columns. A query that matches nothing produces a single item carrying `success: true`. |
| `executeQuery` | **One item per row** when the statement returns rows. A statement that changes data returns a single item with `affectedRows` and `insertId`. A statement with no rows and no counts returns `success: true`. |
| `insert` | One item carrying `affectedRows` and `insertId`. |
| `update`, `upsert` | One item carrying `affectedRows`. |
| `deleteTable` with Command `delete` | One item carrying `affectedRows`. |
| `deleteTable` with Command `truncate` or `drop` | One item carrying `success: true` and `command`. |

A selected row arrives as the row itself:

```json
{
  "id": 42,
  "name": "Ada Lovelace",
  "status": "active",
  "created_at": "2024-01-15T09:30:00.000Z"
}
```

With **Output Query Execution Details** on, an Execute SQL run returns a single item shaped `{ "sql": "…", "data": [ … ] }` instead, so you can inspect the statement that ran.

## Usage Examples

- Select all active users from a MySQL table
- Insert a new row into a MySQL table from input data
- Update customer records matched by ID
- Execute a raw SQL query with parameterized values
- Upsert product data using ON DUPLICATE KEY UPDATE
- Delete rows matching specific conditions

## Example Configuration

Select filtered, sorted rows:

```json
{
  "type": "mysql",
  "parameters": {
    "operation": "select",
    "table": "users",
    "returnAll": true,
    "where": {
      "values": [
        { "column": "status", "condition": "equal", "value": "active" }
      ]
    },
    "sort": {
      "values": [
        { "column": "created_at", "direction": "DESC" }
      ]
    }
  }
}
```

Select a page of distinct rows with a narrowed column list:

```json
{
  "type": "mysql",
  "parameters": {
    "operation": "select",
    "table": "products",
    "returnAll": false,
    "limit": 50,
    "where": {
      "values": [
        { "column": "category", "condition": "equal", "value": "electronics" }
      ]
    },
    "sort": {
      "values": [
        { "column": "price", "direction": "ASC" }
      ]
    },
    "options": {
      "selectDistinct": true,
      "outputColumns": "id,name,price,description"
    }
  }
}
```

Insert each incoming item, mapping its fields onto columns of the same name:

```json
{
  "type": "mysql",
  "parameters": {
    "operation": "insert",
    "table": "analytics_events",
    "dataMode": "autoMapInputData",
    "options": {
      "queryBatching": "transaction",
      "skipOnConflict": true
    }
  }
}
```

Insert with explicit column values:

```json
{
  "type": "mysql",
  "parameters": {
    "operation": "insert",
    "table": "products",
    "dataMode": "defineBelow",
    "valuesToSend": {
      "values": [
        { "column": "name", "value": "Product Name" },
        { "column": "price", "value": "29.99" },
        { "column": "category_id", "value": "5" }
      ]
    }
  }
}
```

Update a row matched on a key column:

```json
{
  "type": "mysql",
  "parameters": {
    "operation": "update",
    "table": "orders",
    "dataMode": "defineBelow",
    "columnToMatchOn": "order_id",
    "valueToMatchOn": "12345",
    "valuesToSend": {
      "values": [
        { "column": "status", "value": "shipped" },
        { "column": "updated_at", "value": "2023-12-01 10:30:00" }
      ]
    }
  }
}
```

Upsert with auto-mapped input:

```json
{
  "type": "mysql",
  "parameters": {
    "operation": "upsert",
    "table": "user_preferences",
    "dataMode": "autoMapInputData",
    "columnToMatchOn": "user_id",
    "options": {
      "replaceEmptyStrings": true
    }
  }
}
```

Run a parameterized query:

```json
{
  "type": "mysql",
  "parameters": {
    "operation": "executeQuery",
    "query": "SELECT u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at >= $1 GROUP BY u.id HAVING order_count > $2",
    "options": {
      "queryReplacement": "2023-01-01,5",
      "detailedOutput": true
    }
  }
}
```

Delete rows matching two conditions:

```json
{
  "type": "mysql",
  "parameters": {
    "operation": "deleteTable",
    "table": "logs",
    "deleteCommand": "delete",
    "where": {
      "values": [
        { "column": "created_at", "condition": "<", "value": "2023-01-01" },
        { "column": "level", "condition": "equal", "value": "debug" }
      ]
    },
    "combineConditions": "AND"
  }
}
```

### Error Handling

| Mode | Behavior |
|------|----------|
| **stop** | Halts workflow on first error |
| **continue** | Skips failed items, passes successful ones through |
| **errorPort** | Routes failed items to Error output port |

## Tips

MySQL database node for executing SQL queries and performing CRUD operations on MySQL tables.

- **Values are always parameterized.** Column names, table names and other identifiers are backtick-escaped, and values are bound rather than interpolated — so a value containing a quote or a semicolon is data, never SQL.
- **Placeholders in Execute SQL are positional.** Write `$1`, `$2`, `$3` for values and `$1:name` for an identifier, then supply the replacements in Options → Query Parameters as one comma-separated list, in order.
- **`Command` on Delete is the difference between a filter and a wipe.** `delete` honours the Select Rows conditions — and removes every row when there are none. `truncate` always empties the table, and `drop` removes the table itself.
- **Update needs something to change.** Every column other than the match column is written; if the only column supplied is the match column, the item fails rather than issuing a no-op statement.
- **Upsert's match column must be a unique key**, otherwise `ON DUPLICATE KEY UPDATE` never triggers and you get duplicate rows.
- **Auto-map sends the whole item.** In `autoMapInputData` mode every property of the incoming item becomes a column, so trim the item upstream if it carries workflow metadata your table does not have.
- **Large numbers default to text.** `BIGINT` and `NUMERIC` columns are returned as strings to avoid precision loss; switch Output Large Numbers As to `numbers` only when the values are small enough to be safe.
- **Keep Max Concurrency low.** Each concurrent item holds a database connection; the default of 1 is deliberate.
- **Select and Execute SQL fan out** — each row becomes its own item, so downstream nodes process rows individually.