MySQL 如何添加一个新列，将行数计算为序列号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5351628/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how can I add a new column which counts the number of rows as serial number
提问by sadi
record of
id fare commission routecode vehicle number productcode date time driver owner name
15 12345 123 4533 1 3344 2011-03-18 00:00:00 yasir saleem
20 a a 3433 1 2333 2011-03-25 00:00:00 yasir saleem
36 11111 11111 3433 1 2333 2011-03-25 16:13:12 yasir saleem
9 1233 123 3433 nk-234 2333 2011-03-24 00:00:00 siddiq aslam
21 1200 120 4533 nk-234 7655 2011-03-24 00:00:00 siddiq aslam
22 1200 133333 0987 nk-234 2333 2011-03-11 00:00:00 siddiq aslam
23 10000 11 4533 nk-234 7655 2011-03-19 00:00:00 siddiq aslam
25 122 12 0987 nk-234 2333 2011-03-11 00:00:00 siddiq aslam
26 1000 100 3344 nk-234 7655 2011-03-11 00:00:00 siddiq aslam
27 1000 100 3344 nk-234 2333 2011-03-10 00:00:00 siddiq aslam
34 100 10 3344 nk-234 2333 2011-03-18 00:00:00 siddiq aslam
35 100 10 3344 nk-234 2333 2011-03-02 00:00:00 siddiq aslam
5 1000 100 1234 wq1233 3344 2011-03-10 22:30:00 waqas sami
6 2222 22 1234 wq1233 3344 2011-03-17 22:30:00 waqas sami
24 a a 4533 PSS-1234 7655 2011-03-02 00:00:00 salman salam
42633 145175
I want to add another column before id which counts the number of
我想在 id 之前添加另一列来计算数量
rows. It should start from 1 and increment by 1 for each row.
行。它应该从 1 开始，每行增加 1。
回答by RichardTheKiwi
If you mean in a SELECT statement:
如果您的意思是在 SELECT 语句中：
Say your select was
说你的选择是
select * from tbl
It becomes
它成为了
select @n := @n + 1 RowNumber, t.*
from (select @n:=0) initvars, tbl t
Notes:
笔记：
select @n:=0is used to reset the global variable to 0@n := @n + 1increases it by 1 for each row, starting from 1. This column is named "RowNumber"
select @n:=0用于将全局变量重置为 0@n := @n + 1每行增加 1，从 1 开始。此列名为“RowNumber”
回答by Schaltwert
Add a new column mySerialto the table myTableand increment each row by 1 (starting at '1'):
mySerial向表中添加一个新列myTable并将每行增加 1（从“1”开始）：
ALTER TABLE myTable ADD mySerial int(11) DEFAULT '0' NOT NULL;
SELECT @n:=0;
UPDATE myTable SET mySerial = @n := @n + 1;
回答by Alex
So, you want to add a column to every row with the rowcount in it? It's not possible to do that automatically, but you can add a column and update it on every insert (UPDATE table SET (rowcount = SELECT COUNT(*) FROM TABLE)) but I wonder why you want to do that? It seems to me you want to workaround something and I think there must be a better solution than adding a rowcount column.
那么，您想向包含行数的每一行添加一列吗？不可能自动执行此操作，但是您可以添加一列并在每次插入 ( UPDATE table SET (rowcount = SELECT COUNT(*) FROM TABLE)) 时更新它，但我想知道您为什么要这样做？在我看来，您想解决一些问题，我认为必须有比添加 rowcount 列更好的解决方案。
回答by Starx
I am not sure if i understand your question completely, but to add a column infront of id run this query
我不确定我是否完全理解您的问题，但是要在 id 前面添加一列，请运行此查询
ALTER TABLE `yourtablename` ADD `yournewfield` VARCHAR( 50 ) NOT NULL BEFORE `id`
回答by Norman H
You may want to look into this blog post: http://jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/
你可能想看看这篇博文：http: //jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/
Seems to have a solution for adding a row number to a query result, which might solve your problem.
似乎有一个向查询结果添加行号的解决方案，这可能会解决您的问题。
