The database has the following list of tables:

dialog_123_1 dialog_123_2 dialog_1_123456 dialog_2_1

All that after after the symbol _ is logins. THAT is, dialog_123_1 is a dialog of user 123 and user 1. I am trying to write a query to get dialogs, for example a user 2. I use this query, but it produces extra tables:

 show TABLEs LIKE 'dialog%2%'

To this request, he issues:

 dialog_123_1 dialog_123_2 dialog_1_123456 dialog_2_1
  • five
    You violated the rules of database design, carried out the foreign key from the data in general in the DDL in the table name, where this data should not be. And now you complain that there are no tools in the DBMS for working with such a scheme? Yes, they are not, SQL (at least in the implementation of MySQL) is not designed to work in this mode. - cheops
  • What are the recommendations? - Nikola Krivosheya
  • Create one dialogs table, with two keys from_id and to_id, in which to store the identifiers of the users participating in the dialog. - cheops 6:55 pm
  • 2
    There is one dialog table and in addition to what is now, another user ID (which is taken from the user table). And it is the ID, not the login - Mike
  • I highly recommend reading the article about data normalization - habrahabr.ru/post/254773 and also "How a relational database works" - habrahabr.ru/company/mailru/blog/266811 - Ella Svetlaya

1 answer 1

Designing a relational database like cheops noted is wrong. Just imagine how many tables you can have, how difficult it will be to work with data and how it will affect speed. Normalize your database and in the simplest form store the dialogs in one table.

If you do not pay attention to the correctness of the design of this database, then to solve a similar problem in MySQL, you need to use the following construct, which uses regular expressions:

  SHOW TABLES WHERE Tables_in_названиеБазы REGEXP "_IdПользователя(_|$)+"

Substitute the name of your database and user id. Example:

  SHOW TABLES WHERE Tables_in_chat REGEXP "_2(_|$){1}"

After the query is executed, all tables that fall under the condition of a regular expression will be displayed.