Description
The MySQL Query component allows you to execute query statements against MySQL to retrieve data.
It is compatible with MySQL 5.6 and above.
Output Payload
| Output Payload | Description |
|---|---|
cv.Payload | Outputs records that match the conditions in the output format specified by the property |
Output example in array format
When table1 has column1 (integer type) and column2 (string type), if you specify `SELECT * FROM table1` in the SQL property and specify 'Array' in the output format property, the output payload will be generated as follows:
Output Example
[1, "abc"]
[2, "def"]
[3, "hij"]Output example in object format
When table1 has column1 (integer type) and column2 (string type), if you specify `SELECT * FROM table1` in the SQL property and specify 'Object Format' in the output format property, the output payload will be generated as follows:
Output Example
{"column1": 1, "column2": "abc"}
{"column1": 2, "column2": "def"}
{"column1": 3, "column2": "hij"}Component Properties
| Property Name | Description |
|---|---|
| SQL | Specifies the SQL statement to execute |
| Output Format | Choose between array format or object format |
| Host | Specifies the host of MySQL |
| Port | Specifies the port of MySQL |
| Username | Specifies the username of MySQL |
| Password | Specifies the password of MySQL |
| Database | Specifies the database |
| Timezone | Specifies the timezone of the database |
| TLS | Specifies whether to encrypt communication, options are: true (connect with SSL/TLS), false (do not connect with SSL/TLS), skip-verify (connect with SSL/TLS but do not verify the server certificate), preferred (try to connect with SSL/TLS first, if unable to connect, try to connect without SSL/TLS) |
| Root Certificate | Specifies the root certificate to use. If using AWS's Aurora or RDS, you will need to use the server certificate issued by AWS. This will be the root certificate of that server certificate |
Placeholders
You can use placeholders in the SQL statement to be executed and use the value of cv.Playload in the SQL statement.
SELECT * FROM table1 WHERE id=?The value of cv.Payload is used for the placeholder specified by ?. When cv.Payload contains 1, it retrieves the record where id is 1.
You can specify more than one placeholder.
SELECT * FROM table1 WHERE name=? AND age=?To specify values for more than one placeholder, specify the values in an array in cv.Payload.
["First Last", 20]Text2SQL
Text2SQL is a function that can generate SQL statements from Japanese entered into the prompt using the functionality of OpenAI's ChatGPT.
For how to use it, please refer to the Text2SQL Usage Guide in the Setup Guide.