You can pass a string with your list and use a prepared statements to run a query, e.g. – DELIMITER $$ CREATE PROCEDURE GetFruits(IN fruitArray VARCHAR(255)) BEGIN SET @sql = CONCAT(‘SELECT * FROM Fruits WHERE Name IN (‘, fruitArray, ‘)’); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END $$ DELIMITER ; How to … Read more
New solution: Built using Your great comments, thanks! SELECT JSON_ARRAYAGG(JSON_OBJECT(‘name’, name, ‘phone’, phone)) from Person; Old solution: With help from @Schwern I managed to put up this query, which seems to work! SELECT CONCAT( ‘[‘, GROUP_CONCAT(JSON_OBJECT(‘name’, name, ‘phone’, phone)), ‘]’ ) FROM person;
What you do here is called a JOIN (although you do it implicitly because you select from multiple tables). This means, if you didn’t put any conditions in your WHERE clause, you had all combinations of those tables. Only with your condition you restrict your join to those rows where the drink id matches. But … Read more
SELECT TIMEDIFF(‘2007-12-31 10:02:00′,’2007-12-30 12:01:01′); — result: 22:00:59, the difference in HH:MM:SS format SELECT TIMESTAMPDIFF(SECOND,’2007-12-30 12:01:01′,’2007-12-31 10:02:00’); — result: 79259 the difference in seconds So, you can use TIMESTAMPDIFF for your purpose.
For inner joins, a single query makes sense, since you only get matching rows. For left joins, multiple queries is much better… look at the following benchmark I did: Single query with 5 Joins query: 8.074508 seconds result size: 2268000 5 queries in a row combined query time: 0.00262 seconds result size: 165 (6 + … Read more
Any time information is one-to-one (each user has one name and password), then it’s probably better to have it one table, since it reduces the number of joins the database will need to do to retrieve results. I think some databases have a limit on the number of columns per table, but I wouldn’t worry … Read more
Here is a simple way to enable them. In mysql we need to see often 3 logs which are mostly needed during any project development. The Error Log. It contains information about errors that occur while the server is running (also server start and stop) The General Query Log. This is a general record of … Read more
SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘{your_db}’; Note from the docs though: For InnoDB tables, the row count is only a rough estimate used in SQL optimization. You’ll need to use COUNT(*) for exact counts (which is more expensive).
Does the –lock-tables=false option work? According to the man page, if you are dumping InnoDB tables you can use the –single-transaction option: –lock-tables, -l Lock all tables before dumping them. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB and BDB, … Read more
This commonly occurs when exporting views/triggers/procedures from one database or server to another as the user that created that object no longer exists. You have two options: 1. Change the DEFINER This is possibly easiest to do when initially importing your database objects, by removing any DEFINER statements from the dump. Changing the definer later … Read more