TiDB

https://docs.pingcap.com/
Admin
TiDB is an open-source, distributed SQL database that supports Hybrid Transactional and Analytical...
Tokens:5,949,651
Snippets:41,215
Trust Score:10
Update:1 week ago
Show doc for...
Tokens:
Raw
### BEGIN Example

Source: https://docs.pingcap.com/tidb/stable/sql-statement-begin

Example session showing how to use BEGIN to start a transaction, insert a row, and commit it.

```sql
mysql> CREATE TABLE t1 (a int NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.12 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
```

--------------------------------

### start-task usage example

Source: https://docs.pingcap.com/tidb/stable/dm-create-task

Example command to start a data migration task using a configuration file and optionally specifying a MySQL source.

```bash
start-task [ -s "mysql-replica-01"] ./task.yaml
```

--------------------------------

### Start a Backup

Source: https://docs.pingcap.com/tidb/stable/sql-statement-show-backups

Starts a backup of the test database to an S3 bucket. This is the first step in the example workflow.

```sql
BACKUP DATABASE `test` TO 's3://example-bucket/backup-01';
```

--------------------------------

### Sample output of TiDB cluster pods

Source: https://docs.pingcap.com/tidb-in-kubernetes/stable/deploy-on-aws-eks

Example output from 'kubectl get pods -n tidb-cluster' showing all pods in Running state. This indicates the TiDB cluster is successfully started.

```text
NAME                              READY   STATUS    RESTARTS   AGE
tidb-discovery-5cb8474d89-n8cxk   1/1     Running   0          47h
tidb-monitor-6fbcc68669-dsjlc     3/3     Running   0          47h
tidb-pd-0                         1/1     Running   0          47h
tidb-pd-1                         1/1     Running   0          46h
tidb-pd-2                         1/1     Running   0          46h
tidb-tidb-0                       2/2     Running   0          47h
tidb-tidb-1                       2/2     Running   0          46h
tidb-tikv-0                       1/1     Running   0          47h
tidb-tikv-1                       1/1     Running   0          47h
tidb-tikv-2                       1/1     Running   0          47h
```

--------------------------------

### Create a virtual environment for the quickstart project

Source: https://docs.pingcap.com/ai/vector-search-integrate-with-sqlalchemy

Changes to the example directory and creates a Python virtual environment, then activates it.

```bash
cd tidb-vector-python/examples/orm-sqlalchemy-quickstart
python3 -m venv .venv
source .venv/bin/activate
```

--------------------------------

### Inspect tidb-drainer values and example values.yaml

Source: https://docs.pingcap.com/tidb-in-kubernetes/stable/deploy-tidb-binlog

Gets the default values.yaml for customization and shows an example that specifies the source TiDB cluster and downstream database. The clusterName and clusterVersion must match the source cluster; initialCommitTs (as a string) sets the start commit timestamp when no checkpoint exists.

```bash
helm inspect values pingcap/tidb-drainer --version=${chart_version} > values.yaml
```

```yaml
clusterName: example-tidb
clusterVersion: v8.1.0
baseImage:pingcap/tidb-binlog
storageClassName: local-storage
storage: 10Gi
initialCommitTs: "-1"
config: |
  detect-interval = 10
  [syncer]
  worker-count = 16
  txn-batch = 20
  disable-dispatch = false
  ignore-schemas = "INFORMATION_SCHEMA,PERFORMANCE_SCHEMA,mysql"
  safe-mode = false
  db-type = "tidb"
  [syncer.to]
  host = "downstream-tidb"
  user = "root"
  password = ""
  port = 4000
```

--------------------------------

### Example: create databases, switch, and query

Source: https://docs.pingcap.com/tidbcloudlake/use-database

A complete example showing how to create two databases, switch between them using USE DATABASE, create tables, insert data, and query the tables.

```sql
-- Create two databases
CREATE DATABASE database1;
CREATE DATABASE database2;

-- Select and use "database1" as the current database
USE database1;

-- Create a new table "table1" in "database1"
CREATE TABLE table1 (
  id INT,
  name VARCHAR(50)
);

-- Insert data into "table1"
INSERT INTO table1 (id, name) VALUES (1, 'John');
INSERT INTO table1 (id, name) VALUES (2, 'Alice');

-- Query all data from "table1"
SELECT * FROM table1;

-- Switch to "database2" as the current database
USE database2;

-- Create a new table "table2" in "database2"
CREATE TABLE table2 (
  id INT,
  city VARCHAR(50)
);

-- Insert data into "table2"
INSERT INTO table2 (id, city) VALUES (1, 'New York');
INSERT INTO table2 (id, city) VALUES (2, 'London');

-- Query all data from "table2"
SELECT * FROM table2;
```

--------------------------------

### CREATE DATABASE Example

Source: https://docs.pingcap.com/tidb/stable/sql-statement-create-database

Example of creating a database, using it, creating a table, and showing tables. Demonstrates the expected output.

```sql
mysql> CREATE DATABASE mynewdatabase;
Query OK, 0 rows affected (0.09 sec)

mysql> USE mynewdatabase;
Database changed
mysql> CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_mynewdatabase |
+-------------------------+
| t1                      |
+-------------------------+
1 row in set (0.00 sec)
```

--------------------------------

### Install NTP service on CentOS 7 with yum

Source: https://docs.pingcap.com/tidb/stable/check-before-deployment

Run this command to manually install the NTP service on CentOS 7. It installs ntp and ntpdate, starts the service, and enables it to start on boot.

```bash
sudo yum install ntp ntpdate && \
sudo systemctl start ntpd.service && \
sudo systemctl enable ntpd.service
```

--------------------------------

### Example dmctl validation start command

Source: https://docs.pingcap.com/tidb/stable/dm-continuous-data-validation

Example command to enable continuous validation for a specific task with a start time and full mode.

```bash
dmctl --master-addr=127.0.0.1:8261 validation start --start-time 2021-10-21T00:01:00 --mode full my_dm_task

```

--------------------------------

### SHOW CREATE USER and SHOW GRANTS examples

Source: https://docs.pingcap.com/tidb/stable/sql-statement-show-create-user

Example commands showing the output of SHOW CREATE USER and SHOW GRANTS for the 'root' user.

```sql
mysql> SHOW CREATE USER 'root';
+--------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for root@%                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

```sql
mysql> SHOW GRANTS FOR 'root';
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)
```

--------------------------------

### Set up sample data for MPP examples

Source: https://docs.pingcap.com/tidb/stable/explain-mpp

Creates a sample table, inserts data, sets a TiFlash replica, analyzes the table, and enables MPP mode with tidb_allow_mpp.

```sql
CREATE TABLE t1 (id int, value int);
INSERT INTO t1 values(1,2),(2,3),(1,3);
ALTER TABLE t1 set tiflash replica 1;
ANALYZE TABLE t1;
SET tidb_allow_mpp = 1;
```

--------------------------------

### GET and ARRAY_GET example

Source: https://docs.pingcap.com/tidbcloudlake/get-sql

Example query using GET and its alias ARRAY_GET to retrieve the second element from an array, with the resulting output table.

```sql
SELECT GET([1, 2], 2), ARRAY_GET([1, 2], 2);
```

```text
┌───────────────────────────────────────┐
│ get([1, 2], 2) │ array_get([1, 2], 2) │
├────────────────┼──────────────────────┤
│              2 │                    2 │
└───────────────────────────────────────┘
```

--------------------------------

### SHOW CREATE DATABASE example with output

Source: https://docs.pingcap.com/tidbcloudlake/show-create-database

Example of SHOW CREATE DATABASE on the 'default' database, showing the resulting table with the CREATE DATABASE statement.

```sql
SHOW CREATE DATABASE default;
+----------+---------------------------+
| Database | Create Database           |
+----------+---------------------------+
| default  | CREATE DATABASE `default` |
+----------+---------------------------+
```

--------------------------------

### Get current time

Source: https://docs.pingcap.com/tidb/stable/as-of-timestamp

Get the current time, used as the timestamp for later examples.

```sql
select now();
```

--------------------------------

### Start Flink SQL client

Source: https://docs.pingcap.com/tidb/stable/replicate-data-to-kafka

Starts the Flink SQL client in the Flink installation directory.

```bash
[root@flink flink-1.15.0]# ./bin/sql-client.sh
```

--------------------------------

### Example output from the quickstart demo

Source: https://docs.pingcap.com/ai/vector-search-integrate-with-sqlalchemy

Shows the expected output of the demo, including nearest neighbor search results and documents within a certain distance.

```text
Get 3-nearest neighbor documents:
  - distance: 0.00853986601633272
    document: fish
  - distance: 0.12712843905603044
    document: dog
  - distance: 0.7327387580875756
    document: tree
Get documents within a certain distance:
  - distance: 0.00853986601633272
    document: fish
  - distance: 0.12712843905603044
    document: dog
```

--------------------------------

### Initialize env.sh from example

Source: https://docs.pingcap.com/tidb/stable/dev-guide-sample-application-java-jdbc

Command to copy the example environment file and the resulting configuration structure.

```bash
cp env.sh.example env.sh
```

```bash
export TIDB_HOST='{host}'
export TIDB_PORT='4000'
export TIDB_USER='root'
export TIDB_PASSWORD='{password}'
export TIDB_DB_NAME='test'
export USE_SSL='false'
```

--------------------------------

### Start only the PD component with -R pd

Source: https://docs.pingcap.com/tidb/stable/maintain-tidb-using-tiup

Starts only the PD component of the cluster. When using `-R` or `-N`, ensure the starting order is correct; for example, start PD before TiKV, otherwise the start might fail.

```bash
tiup cluster start ${cluster-name} -R pd
```

--------------------------------

### TO_START_OF_HOUR example

Source: https://docs.pingcap.com/tidbcloudlake/to-start-of-hour

Example query that rounds down the given timestamp to the start of the hour, returning '2023-11-12 09:00:00'.

```sql
SELECT
  to_start_of_hour('2023-11-12 09:38:18.165575');

┌────────────────────────────────────────────────┐
│ to_start_of_hour('2023-11-12 09:38:18.165575') │
├────────────────────────────────────────────────┤
│ 2023-11-12 09:00:00                            │
└────────────────────────────────────────────────┘
```

--------------------------------

### Create index and compare EXPLAIN output before and after

Source: https://docs.pingcap.com/tidb/stable/sql-statement-create-index

Shows a full workflow: create a table, insert sample rows, run EXPLAIN before creating an index, create the index, then run EXPLAIN again to see the improved plan. The before-plan uses TableFullScan_5 with stats:pseudo, and the after-plan uses IndexLookUp with IndexRangeScan.

```sql
mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 10.00    | root      |               | data:Selection_6               |
| └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.t1.c1, 3)              |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> CREATE INDEX c1 ON t1 (c1);
Query OK, 0 rows affected (0.30 sec)

mysql> EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
+------------------------+---------+-----------+------------------------+--------------------------------+
| id                     | estRows | task      | access object          | operator info                  |
+------------------------+---------+-----------+------------------------+--------------------------------+
| IndexReader_6          | 10.00   | root      |                       | index:IndexRangeScan_5         |
| └─IndexRangeScan_5     | 10.00   | cop[tikv] | table:t1, index:c1(c1) | range:[
```

--------------------------------

### Quick Setup: Create Table, Aggregating Index, Refresh, and Verify

Source: https://docs.pingcap.com/tidbcloudlake/aggregating-index

Creates a sample sales table, defines an aggregating index for common analytics, manually refreshes it, and verifies its usage with EXPLAIN.

```sql
-- Create table with sample data
CREATE TABLE sales(region VARCHAR, product VARCHAR, revenue DECIMAL, quantity INT);

-- Create aggregating index for common analytics
CREATE AGGREGATING INDEX sales_summary AS
SELECT region, SUM(revenue), COUNT(*), AVG(quantity)
FROM sales
GROUP BY region;

-- Refresh the index (manual mode)
REFRESH AGGREGATING INDEX sales_summary;

-- Verify the index is used
EXPLAIN SELECT region, SUM(revenue) FROM sales GROUP BY region;
```

--------------------------------

### Start n8n with npx

Source: https://docs.pingcap.com/tidbcloud/integrate-tidbcloud-with-n8n

Use this command to start n8n after installing Node.js. It runs n8n via npx, which downloads and starts the tool. After starting, visit localhost:5678 to access the n8n interface.

```bash
npx n8n
```

--------------------------------

### SHOW COLUMNS examples

Source: https://docs.pingcap.com/tidbcloudlake/show-columns

Examples showing CREATE TABLE, SHOW COLUMNS, SHOW FULL COLUMNS, and SHOW FULL COLUMNS with LIKE filter, along with their output tables.

```sql
CREATE TABLE books
  (
     price  FLOAT Default 0.00,
     pub_time DATETIME Default '1900-01-01',
     author VARCHAR
  );

SHOW COLUMNS FROM books FROM default;

Field   |Type     |Null|Default     |Extra|Key|
--------+---------+----+------------+-----+---+
author  |VARCHAR  |NO  |            |     |   |
price   |FLOAT    |NO  |0.00        |     |   |
pub_time|TIMESTAMP|NO  |'1900-01-01'|     |   |

SHOW FULL COLUMNS FROM books;

Field   |Type     |Null|Default     |Extra|Key|Collation|Privileges|Comment|
--------+---------+----+------------+-----+---+---------+----------+-------+
author  |VARCHAR  |NO  |            |     |   |         |          |       |
price   |FLOAT    |NO  |0.00        |     |   |         |          |       |
pub_time|TIMESTAMP|NO  |'1900-01-01'|     |   |         |          |       |

SHOW FULL COLUMNS FROM books LIKE 'a%'

Field |Type   |Null|Default|Extra|Key|Collation|Privileges|Comment|
------+-------+----+-------+-----+---+---------+----------+-------+
author|VARCHAR|NO  |       |     |   |         |          |       |
```

--------------------------------

### TO_START_OF_DAY example query and output

Source: https://docs.pingcap.com/tidbcloudlake/to-start-of-day

Example query that rounds down a timestamp to the start of the day, with the resulting output table.

```sql
SELECT
  to_start_of_day('2023-11-12 09:38:18.165575');
```

```text
┌───────────────────────────────────────────────┐
│ to_start_of_day('2023-11-12 09:38:18.165575') │
├───────────────────────────────────────────────┤
│ 2023-11-12 00:00:00                           │
└───────────────────────────────────────────────┘
```

--------------------------------

### Create and populate orders_window_demo table

Source: https://docs.pingcap.com/tidbcloudlake/first-value

Sets up the sample table and data used by all FIRST_VALUE examples on this page. Run this first to have the orders_window_demo table available.

```sql
-- Sample order data
CREATE OR REPLACE TABLE orders_window_demo (
    customer VARCHAR,
    order_id INT,
    order_time TIMESTAMP,
    amount INT,
    sales_rep VARCHAR
);

INSERT INTO orders_window_demo VALUES
    ('Alice', 1001, to_timestamp('2024-05-01 09:00:00'), 120, 'Erin'),
    ('Alice', 1002, to_timestamp('2024-05-01 11:00:00'), 135, NULL),
    ('Alice', 1003, to_timestamp('2024-05-02 14:30:00'), 125, 'Glen'),
    ('Bob',   1004, to_timestamp('2024-05-01 08:30:00'),  90, NULL),
    ('Bob',   1005, to_timestamp('2024-05-01 20:15:00'), 105, 'Kai'),
    ('Bob',   1006, to_timestamp('2024-05-03 10:00:00'),  95, NULL),
    ('Carol', 1007, to_timestamp('2024-05-04 09:45:00'),  80, 'Lily');

```

--------------------------------

### Install lake-go driver

Source: https://docs.pingcap.com/tidbcloudlake/connect-using-golang

Install the official Go driver for TiDB Cloud Lake using go get.

```bash
go get github.com/tidbcloud/lake-go
```

--------------------------------

### SHOW CREATE DATABASE Example

Source: https://docs.pingcap.com/tidbcloud/sql-statement-show-create-database

Shows the CREATE DATABASE statement for the 'test' database.

```sql
SHOW CREATE DATABASE test;
```

--------------------------------

### Deploy with helm install

Source: https://docs.pingcap.com/tidb-in-kubernetes/stable/cheat-sheet

Deploys a chart into the specified namespace with version and custom values file; example installs tidb-operator.

```shell
helm install ${name} ${chart_name} --namespace=${namespace} --version=${chart_version} -f ${values_file}
```

```shell
helm install tidb-operator pingcap/tidb-operator --namespace=tidb-admin --version=v1.6.6 -f values-tidb-operator.yaml
```

--------------------------------

### GRANT ALL example with CREATE USER and SHOW GRANTS

Source: https://docs.pingcap.com/tidb/stable/sql-statement-grant-privileges

Example session showing how to create a user, grant all privileges on a database, and display the resulting grants. The output shows the granted privileges, including the implicit USAGE privilege.

```sql
mysql> CREATE USER 'newuser' IDENTIFIED BY 'mypassword';
Query OK, 1 row affected (0.02 sec)

mysql> GRANT ALL ON test.* TO 'newuser';
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW GRANTS FOR 'newuser';
+-------------------------------------------------+
| Grants for newuser@%                            |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'newuser'@'%'             |
| GRANT ALL PRIVILEGES ON test.* TO 'newuser'@'%' |
+-------------------------------------------------+
2 rows in set (0.00 sec)
```

--------------------------------

### tiproxyctl example with --host and --port

Source: https://docs.pingcap.com/tidb/stable/tiproxy-command-line-flags

Example of using tiproxyctl to get configuration from a TiProxy server at 127.0.0.1:3080.

```bash
tiproxyctl --host 127.0.0.1 --port 3080 config get
```

--------------------------------

### Install TiProxy Control using TiUP

Source: https://docs.pingcap.com/tidb/stable/tiproxy-command-line-flags

After installing TiUP, use `tiup install tiproxy` to download and install TiProxy and TiProxy Control. The example shows the download output and how to locate the tiproxyctl binary in the same directory as TiProxy.

```bash
tiup install tiproxy
# download https://tiup-mirrors.pingcap.com/tiproxy-v1.3.0-linux-amd64.tar.gz 22.51 MiB / 22.51 MiB 100.00% 13.99 MiB/s
ls `tiup --binary tiproxy`ctl
# /root/.tiup/components/tiproxy/v1.3.0/tiproxyctl
```

--------------------------------

### DESCRIBE TABLE example with CREATE TABLE and output

Source: https://docs.pingcap.com/tidbcloudlake/describe-table

Creates a sample books table and then describes it, showing the resulting column information including types, nullability, and defaults.

```sql
CREATE TABLE books
  (
     price  FLOAT Default 0.00,
     pub_time DATETIME Default '1900-01-01',
     author VARCHAR
  );

DESC books;

Field   |Type     |Null|Default                     |Extra|
--------+---------+----+----------------------------+-----+
price   |FLOAT    |YES |0                           |     |
pub_time|TIMESTAMP|YES |'1900-01-01 00:00:00.000000'|     |
author  |VARCHAR  |YES |NULL                        |     |

```
Tokens:
Raw