英文:
How to make char with pattern as sql id?
问题
我知道很多人认为使用char作为主键是一种不好的做法，但在这个项目中我需要它。因此，我正在创建一个API，从http参数中接收图书ID。我只接收一个单一的ID，但数据可能来自两个表，一个是publicBooks，另一个是privateBooks。我可以查询两个表中的ID，但我需要一种找到要查询的表的方法。所以我决定在我的privateBooks表中使用char，这样ID看起来像这样：
p-1
p-2
p-3
...
这样，我可以查看http请求是否包含p-，如果是，我将查找privateBooks，否则我将查找publicBooks。但我不知道如何自动将ID设置为p-i，其中i是自增整数。有人知道如何做到这一点吗？
英文:
So I know that many argue that char as primary key is a bad practice, but I need it in this project. So I creating an API that receive book id from http params. I only received a single id, but the data can come from 2 tables, one is publicBooks and another is privateBooks. I can do query to search the id in both, but I need a way to find the table to search. So I decide to use char to my privateBooks table so the id will look like:
p-1
p-2
p-3
...
So I can see if the http req has p- then i will look in privateBooks, else i will look at publicBooks. But I don't find a way to automatically set the id as p-i, with i as auto increment integer. Anyone know how to do this?
答案1
得分: 0
你不需要改变你的数据库。只需选择一些指示表格的内容。这可以是'p'，1/2，甚至更好的是表格名称。所以，如果你得到参数1和'publicBooks'，你知道这是关于publicBooks中ID为1的内容。
以下是一个查找作者在两个表中的书籍的示例：
select id, 'publicBooks' as tbl from publicBooks where author_id = 123
union all
select id, 'privateBooks' as tbl from privateBooks where author_id = 123;
英文:
You don't have to change your database. Just select something along that tells you the table. This can be 'p' or 1/2 or even better the table name. So, if you get the parameters 1 and 'publicBooks', you know this is about the ID 1 in publicBooks.
Here is an example that looks for an author's books in both tables:
select id, 'publicBooks' as tbl from publicBooks where author_id = 123
union all
select id, 'privateBooks' as tbl from privateBooks where author_id = 123;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论