Code: Select all
SELECT GROUP_CONCAT(id) FROM JSON_TABLE(@json, '$[*]' COLUMNS (id INT UNSIGNED PATH '$')) `ids` GROUP BY id;
Code: Select all
SET @json = CONVERT('[1095, 1106, 1193, 1288, 1357, 1385, 1390]', JSON);
SELECT ids.* FROM JSON_TABLE(@json, '$[*]' COLUMNS (id INT UNSIGNED PATH '$')) `ids`;
Please fix this error - it took me forever to realize there was no issue with the syntax and it is a weird error that only occurs in dbForge. Thank you.You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`json_table` WHERE non_unique = 0' at line 1
Here is a stored procedure that runs and retrieves all data from a table, including invisible columns. The caveat to this method is that it requires the use of a prepared statement in order to access the information_schema table directly - so I am not sure if that is something the IDE can do when user selects to view table data:dzhanhira wrote: ↑Thu 19 May 2022 10:03
When executing the Retrieve Data command, we send a SELECT * FROM <table_name> query to the server;
You have assigned the INVISIBLE attribute to one of the columns. This means that this column (with data) will not appear in the list of columns in the result of a SELECT * FROM <table_name> query. In this case, the INVISIBLE column is part of the PRIMARY KEY. As a result, the returned data set does not contain KEY columns that allow you to safely perform data editing operations.
Code: Select all
CREATE PROCEDURE `get_data`(IN v_table VARCHAR(255))
READS SQL DATA
BEGIN
DECLARE v_query VARCHAR(5000);
SET SESSION group_concat_max_len = 1024000;
SELECT CONCAT('SELECT ', GROUP_CONCAT(distinct c.COLUMN_NAME ORDER BY c.ORDINAL_POSITION) , ' FROM ',v_table, ' LIMIT 1000;') INTO v_query from information_schema.`COLUMNS` c where c.table_name = v_table and table_schema=DATABASE();
SET @sql = v_query;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
Code: Select all
mysql> SELECT TABLE_NAME, COLUMN_NAME, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
+------------+-------------+-----------+
| TABLE_NAME | COLUMN_NAME | EXTRA |
+------------+-------------+-----------+
| t1 | i | |
| t1 | j | |
| t1 | k | INVISIBLE |
+------------+-------------+-----------+
Code: Select all
mysql> SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price' RETURNING DECIMAL(4,2)) AS price;
+-------+
| price |
+-------+
| 49.95 |
+-------+