子查询错误或编译问题？ [英] subquery bug or compilation prob?
问题描述
亲爱的SQL向导，
我在mysql下的子查询有点问题，我想要
知道你是否可以帮助我。
这个例子来自参考手册：
http://www.doctaur.com/dtdocs/databa...anual_437.html
以下是我得到的当我运行那个简单的查询时：
$ cat test.sql
DROP DATABASE test;
CREATE DATABASE test;
connect test;
CREATE TABLE t1（s1 INT）;
INSERT INTO t1 VALUES（1）;
CREATE TABLE t2（s1 INT）;
INSERT INTO t2 VALUES（2）;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT（SELECT s1 FROM t2）FROM t1;
$ / usr / local / mysql / bin / mysql -t -u root -p< test.sql
输入密码：
+ ------ +
| s1 |
+ ------ +
| 1 |
+ ------ +
+ ------ +
| s1 |
+ ------ +
| 2 |
+ ------ +
第14行的ERROR 1064：您的SQL语法出错。检查
与您的MySQL服务器版本对应的手册，以便在''SELECT s1 FROM t2'附近使用正确的
语法来自第1行的FROM t1''
现在我的盒子上有一些细节：
mysql>状态
--------------
/ usr / local / mysql / bin / mysql Ver 12.22 Distrib 4.0.18，for pc -linux
（i686）
连接数：229
当前数据库：
当前用户：root @ localhost
SSL：未使用
当前寻呼机：stdout
使用outfile：''''
服务器版本：4.0.18-log
协议版本：10
连接：通过UNIX插槽的Localhost
客户端字符集：latin1
服务器字符集：latin1
UNIX套接字：/tmp/mysql.sock
正常运行时间：1天24分21秒
主题：1个问题：5389慢查询：0打开：109刷新
表：1打开表：30每秒查询平均值：0.061
请注意，您在safe_update_mode中运行：
不使用密钥的UPDATE和DELETE WHERE子句不允许使用
。
（可以通过在
命令末尾添加LIMIT＃强制UPDATE / DELETE。 ）
如果不使用LIMIT，SELECT有一个自动''LIMIT 1000'。
连接中检查的最大行组合数设置为：1000000
--------------
PIII 900 Mhz / 512 RAM / Slack 9.1 / Kernel（vanilla）2.6 .4
如何编译mysql ...如果我可以信任我的笔记：
＃CFLAGS =" -O3 -mcpu = pentiumpro" CXX = gcc CXXFLAGS =" -O3
-mcpu = pentiumpro -felide-constructors -fno-exceptions -fno-rtti"
../configure --prefix = / usr / local / mysql --enable-assembler
--enable-safe-thread-client --with-mysql-user = mysql --with-openssl
--with-unix-socket-path = / tmp / mysql.sock
＃make
＃make install
非常感谢你的帮助
Ed。
Dear SQL wizards,
I''m having a little problem with sub queries under mysql and I''d like
to know if you can help me out.
The example is streight out from the ref manual:
http://www.doctaur.com/dtdocs/databa...anual_437.html
The following is what I get when I run that simple query:
$ cat test.sql
DROP DATABASE test;
CREATE DATABASE test;
connect test;
CREATE TABLE t1 (s1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);
SELECT * FROM t1;
SELECT * FROM t2;
SELECT (SELECT s1 FROM t2) FROM t1;
$ /usr/local/mysql/bin/mysql -t -u root -p < test.sql
Enter password:
+------+
| s1 |
+------+
| 1 |
+------+
+------+
| s1 |
+------+
| 2 |
+------+
ERROR 1064 at line 14: 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 s1 FROM t2) FROM t1'' at line 1
Now some details on my box:
mysql> status
--------------
/usr/local/mysql/bin/mysql Ver 12.22 Distrib 4.0.18, for pc-linux
(i686)
Connection id: 229
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''''
Server version: 4.0.18-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 1 day 24 min 21 sec
Threads: 1 Questions: 5389 Slow queries: 0 Opens: 109 Flush
tables: 1 Open tables: 30 Queries per second avg: 0.061
Note that you are running in safe_update_mode:
UPDATEs and DELETEs that don''t use a key in the WHERE clause are not
allowed.
(One can force an UPDATE/DELETE by adding LIMIT # at the end of the
command.)
SELECT has an automatic ''LIMIT 1000'' if LIMIT is not used.
Max number of examined row combination in a join is set to: 1000000
--------------
PIII 900 Mhz / 512 RAM / Slack 9.1 / Kernel (vanilla) 2.6.4
How it mysql was compiled... if I can trust my notes:
# CFLAGS="-O3 -mcpu=pentiumpro " CXX=gcc CXXFLAGS="-O3
-mcpu=pentiumpro -felide-constructors -fno-exceptions -fno-rtti"
../configure --prefix=/usr/local/mysql --enable-assembler
--enable-safe-thread-client --with-mysql-user=mysql --with-openssl
--with-unix-socket-path=/tmp/mysql.sock
# make
# make install
Many thanks for your help
Ed.
推荐答案
cat test.sql
DROP DATABASE测试;
CREATE DATABASE测试;
连接测试;
CREATE TABLE t1（s1 INT）;
INSERT INTO t1 VALUES（1）;
CREATE TABLE t2（s1 INT）;
INSERT INTO t2 VALUES（2）;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT（SELECT s1 FROM t2）FROM t1;
cat test.sql
DROP DATABASE test;
CREATE DATABASE test;
connect test;
CREATE TABLE t1 (s1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);
SELECT * FROM t1;
SELECT * FROM t2;
SELECT (SELECT s1 FROM t2) FROM t1;
/ usr / local / mysql / bin / mysql -t -u root -p< test.sql
输入密码：
+ ------ +
| s1 |
+ ------ +
| 1 |
+ ------ +
+ ------ +
| s1 |
+ ------ +
| 2 |
+ ------ +
第14行的ERROR 1064：您的SQL语法出错。检查
与您的MySQL服务器版本对应的手册，以便在''SELECT s1 FROM t2'附近使用正确的
语法来自第1行的FROM t1''
现在我的盒子上有一些细节：
mysql>状态
--------------
/ usr / local / mysql / bin / mysql Ver 12.22 Distrib 4.0.18，for pc -linux
（i686）
连接数：229
当前数据库：
当前用户：root @ localhost
SSL：未使用
当前寻呼机：stdout
使用outfile：''''
服务器版本：4.0.18-log
协议版本：10
连接：通过UNIX插槽的Localhost
客户端字符集：latin1
服务器字符集：latin1
UNIX套接字：/tmp/mysql.sock
正常运行时间：1天24分21秒
主题：1个问题：5389慢查询：0打开：109刷新
表：1打开表：30每秒查询平均值：0.061
请注意，您在safe_update_mode中运行：
不使用密钥的UPDATE和DELETE WHERE子句不允许使用
。
（可以通过在
命令末尾添加LIMIT＃强制UPDATE / DELETE。 ）
如果不使用LIMIT，SELECT有一个自动''LIMIT 1000'。
连接中检查的最大行组合数设置为：1000000
--------------
PIII 900 Mhz / 512 RAM / Slack 9.1 / Kernel（vanilla）2.6 .4
如何编译mysql ...如果我可以信任我的笔记：
＃CFLAGS =" -O3 -mcpu = pentiumpro" CXX = gcc CXXFLAGS =" -O3
-mcpu = pentiumpro -felide-constructors -fno-exceptions -fno-rtti"
../configure --prefix = / usr / local / mysql --enable-assembler
--enable-safe-thread-client --with-mysql-user = mysql --with-openssl
--with-unix-socket-path = / tmp / mysql.sock
＃make
＃make install
非常感谢你的帮助
Ed。
/usr/local/mysql/bin/mysql -t -u root -p < test.sql
Enter password:
+------+
| s1 |
+------+
| 1 |
+------+
+------+
| s1 |
+------+
| 2 |
+------+
ERROR 1064 at line 14: 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 s1 FROM t2) FROM t1'' at line 1
Now some details on my box:
mysql> status
--------------
/usr/local/mysql/bin/mysql Ver 12.22 Distrib 4.0.18, for pc-linux
(i686)
Connection id: 229
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''''
Server version: 4.0.18-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 1 day 24 min 21 sec
Threads: 1 Questions: 5389 Slow queries: 0 Opens: 109 Flush
tables: 1 Open tables: 30 Queries per second avg: 0.061
Note that you are running in safe_update_mode:
UPDATEs and DELETEs that don''t use a key in the WHERE clause are not
allowed.
(One can force an UPDATE/DELETE by adding LIMIT # at the end of the
command.)
SELECT has an automatic ''LIMIT 1000'' if LIMIT is not used.
Max number of examined row combination in a join is set to: 1000000
--------------
PIII 900 Mhz / 512 RAM / Slack 9.1 / Kernel (vanilla) 2.6.4
How it mysql was compiled... if I can trust my notes:
# CFLAGS="-O3 -mcpu=pentiumpro " CXX=gcc CXXFLAGS="-O3
-mcpu=pentiumpro -felide-constructors -fno-exceptions -fno-rtti"
../configure --prefix=/usr/local/mysql --enable-assembler
--enable-safe-thread-client --with-mysql-user=mysql --with-openssl
--with-unix-socket-path=/tmp/mysql.sock
# make
# make install
Many thanks for your help
Ed.
Edoardo Costa写道：
Edoardo Costa wrote:
I我在mysql下的子查询有点问题，我想要
服务器版本：4.0.18-log
I''m having a little problem with sub queries under mysql and I''d like Server version: 4.0.18-log
支持子查询版本4.1，你有4.0，这是你的
问题。
Subqueries are supported from version 4.1, you have 4.0, there''s your
problem.
这篇关于子查询错误或编译问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
