MySQL让我疯了！ [英] MySQL is making me nuts!
问题描述
大家好，这可能更适合MySQL新闻组，但是我想b'b $我想在这里发帖，看看是否有人可以帮助我。
我正在尝试创建一个简单的交易处理系统，用户可以通过某种点来互相支付额外费用。
我有一个表格，用于存储每笔交易，单行。
看起来像这样
transid，from_user，from_amount，to_user，to_amount
鉴于上述情况，我只想要一个简单的单个SQL语句，它可以使
返回一个完整的余额。
现在我最初通过查询数据库在PHP中执行此操作。
$ query [0] =" SELECT SUM（to_amount）WHERE to_user =''$ user-> uid''" ;;
$ query [1] =" SELECT SUM（from_amount）WHERE from_user =''$ user-> uid''" ;;
$ deposits = mysql_result（mysql_query（$ query [0]） ;
$ withdrawls = mysql_result（mysql_query（$ query [1]）;
$ balance = $ deopsits - $ withdrawls;
这很好用，所以我填充了DB超过100,000条记录和
现在页面加载需要75总资产负债表页面的-80秒（我只需要循环每个用户，并且有100个用户），并且单个用户查询的价格超过
10秒。
分析了探查器中的代码（我正在使用Zend）后，我发现
最大的优化我可以做的就是切出来2个查询
变量赋值和简单数学（基本上都是上面的所有代码），
并将其移动到一个完成所有数学运算的SQL语句中。
这应该是基本的，但不幸的是，MySQL不喜欢我的
解决方案，并且无处不在地搜索，我似乎无法找到
类似的东西都经过了尝试。但是来吧，我知道我不能成为历史上第一个对此类查询有类似需求的人。
这是我能想到的最优雅的解决方案，一切都是原始的
SQL荣耀。
SELECT SUM（to_amount - from_amount）作为余额FROM
（（SELECT from_amount as withdrawls FROM transactions WHERE from_user =
''1''）
（SELECT to_amount as deposit FROM transactions WHERE to_user = ''1''））;
可悲的是，尽管每一项措施我都能找到它，但它只是
不是。 br />
我一直得到
＃1064 - 你的SQL语法有错误;检查
对应于你的MySQL服务器版本的手册，使用正确的语法
''附近（SELECT to_amount as deposit FROM transactions WHERE to_user =
''1''）'''在第3行
我只是没有看到它，据我所知，这应该有效。
任何想法？
query [0] =" SELECT SUM（to_amount）WHERE to_user =''
user-> uid''" ;;
query [1] =" SELECT SUM（from_amount）WHERE from_user = ''
Hello all, this might better be suited for the MySQL newsgroup, but I
figured I''ld post here and see if anyone can help me.
I''m trying to create a simple transaction handling system where users
pay eachother via points of a sort.
I have a table where each transaction is stored, in a single row.
It looks like this
transid,from_user,from_amount,to_user,to_amount
Given the above, I just want a simple single SQL statement that can
return a full balance.
Now originally I did this in PHP by querying the DB with the following.
$query[0] = "SELECT SUM(to_amount) WHERE to_user = ''$user->uid''";
$query[1] = "SELECT SUM(from_amount) WHERE from_user = ''$user->uid''";
$deposits = mysql_result(mysql_query($query[0]);
$withdrawls = mysql_result(mysql_query($query[1]);
$balance = $deopsits - $withdrawls;
This works great, so I populated the DB with over 100,000 records and
now page loads take 75-80 seconds for the total balance sheet page (I
just loop through each user, and there are 100 users), and upwards of
10 seconds on a single user query.
After analyzing the code in the profiler (I''m using Zend), I found the
largest optimization I could make would be to cut out the 2 queries
variable assignment and simple math (basically all of the code above),
and move it into a single SQL statement that does all the math.
This SHOULD be elementary, but unfortunately, MySQL doesn''t like my
solution, and searching everywhere I could, I can''t seem to find where
anything similar has every been tried. But come on, I know I can''t be
the first person in history with a similar need for a query like this.
Here is the most elegant solution I could come up with, in all it''s raw
SQL glory.
SELECT SUM(to_amount - from_amount) as balance FROM
((SELECT from_amount as withdrawls FROM transactions WHERE from_user =
''1'')
(SELECT to_amount as deposits FROM transactions WHERE to_user = ''1''));
Sadly, even though by every measure I can find it SHOULD work, it just
doesn''t.
I keep getting
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ''(SELECT to_amount as deposits FROM transactions WHERE to_user =
''1''))'' at line 3
I''m just not seeing it, as far as I can tell this should work.
Any Ideas?
query[0] = "SELECT SUM(to_amount) WHERE to_user = ''
user->uid''";
query[1] = "SELECT SUM(from_amount) WHERE from_user = ''
这篇关于MySQL让我疯了！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
