从更多相同的表创建一个大表

Long time ago I created application which contains database with table for each user and every table has only 1 row. So I have tables like user1, user2, ..., user2800. Now I know it was a terrible idea so I am trying to create one big table for every user. I need to create table

很久以前我创建的应用程序包含每个用户的表数据库,每个表只有1行。所以我有像user1,user2,...,user2800这样的表。现在我知道这是一个糟糕的主意,所以我想为每个用户创建一个大表。我需要创建表

CREATE TABLE `users` (`user_id`, `col1`, `col2`, ...)

From tables like:

来自以下表:

TABLE `user###` (`row_id`, `col1`, `col2`, ...)

where ### is id of user.

其中###是用户的id。

Is there any good way how to do it?

有什么好办法怎么做?

2 个解决方案

#1

Since all user tables have the same fields, you just have to create the unique table then loop over all user tables to retrieve their content.

由于所有用户表都具有相同的字段,因此您只需创建唯一表,然后遍历所有用户表以检索其内容。

For example with PHP, it could be like this:

例如,使用PHP,它可能是这样的:

$sql = CREATE TABLE `users` (`user_id`, `col1`, `col2`, `col3`);
$mysqli_query($link, $sql);

$nbContacts = 2800;
for($i = 1;$i <= $nbContacts; $i++)
{
    $sql = 'INSERT INTO users(`user_id`, `col1`, `col2`, `col3`) SELECT `'.$i.'`, `col1`, `col2`, `col3` FROM `user'.$i.'`';
    $mysqli_query($link, $sql);
}

#2

Don't try to do this in sql, just script it in your favorite language. In pseudocode:

不要尝试在sql中执行此操作,只需使用您喜欢的语言编写脚本即可。在伪代码中:

$tables = get_tables();  // write funciton that uses "show tables from books;"
foreach $tables as $table {
    $userid = getIdFromTableName($table); // simple regexp
    $data = getRowFromTable($table); // get data from old table
    insertIntoUser($userid, $data); // inserts your userid and the data in the new table
}

It's too broad to figure out what your favorite language is, and what the specifics creating your script is, but this is what you should do.

它太宽泛了,无法弄清楚你最喜欢的语言是什么,以及创建脚本的细节是什么,但这是你应该做的。

#1