SQL Query to SELECT from multiple Schema's-CodePudding

I have an database with alot of users (schema's) for example:

user_1
user_2
user_3
...
user_303

All of those got the same tables, for example:

CREATE TABLE `messages` (
  `id` int(11) NOT NULL,
  `content` text COLLATE utf8mb3_unicode_ci NOT NULL,
  `date` datetime NOT NULL,
  `viewed` int(11) NOT NULL,
  `forId` int(11) NOT NULL,
  `fromId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

INSERT INTO `messages` (`id`, `content`, `date`, `viewed`, `forId`, `fromId`) VALUES
(1, 'Hello World', '2020-06-04 14:49:17', 1, 2106, 1842),
(2, 'Hi there', '2020-06-04 14:49:39', 1, 2106, 1842),
(3, 'test 1', '2022-01-03 11:40:43', 1, 3006, 3006),
(4, 'Test 2', '2022-01-20 12:01:52', 1, 1842, 1842);

What I want is a query for example:

USE user_1, user_2, user_3;

SELECT * FROM `messages` WHERE `content` LIKE `%Hi%`;

I don't know if this is possible as a SQL Query, an other option is to write a small PHP code with a for each loop but than I want a command so I get an list of all users: user_1 till user_303

The users are not from 1 till 303 there are some users deleted, to it can be that user_200 doesn't exist any more.

Hope someone here can help me out

CodePudding user response：

You can use the following to write the query you want.

USE information_schema;

SELECT concat("SELECT * FROM ", table_schema,".",table_name, " UNION ALL ") 
FROM tables WHERE table_name = 'messages';

You will obtain something like this;

SELECT * FROM base.messages UNION ALL
SELECT * FROM c.messages UNION ALL

You can then run this query to obtain what you want.

CodePudding user response：

No. The USE statement switches the database, not schema. The select statement uses the default schema unless you specify one. There is no mechanism for having a table that exists across schemas, each table will be its own independent object and you will have to query each one.

You should probably use one 'Messages' table in a separate schema with the user as one of the columns.