Mysql
如何將表合併到mysql中的新表中?
我有一張如下表:
id col1 col2 col3 type 1 12 12 23 1 2 23 33 1 0 3 333 2 21 1 .....
我嘗試選擇 type=1 的 1% 樣本和 type=0 的所有數據,我編寫的程式碼如下:
create table tbl2( SELECT * FROM tbl1 WHERE type=1 AND RAND() <= 0.01 UNION SELECT * FROM tbl1 WHERE type =0 );
它最終成為一個錯誤:
ERROR 1064 (42000): 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 'UNION SELECT * FROM tbl1 WHERE type =0 )' at line 3
任何人都可以幫助我嗎?
我剛剛將它載入到我的 Windows 筆記型電腦上的 MySQL 5.5.37 中
use test drop table if exists tbl1; create table tbl1 (id int not null, col1 int not null, col2 int not null, col3 int not null, type int not null); insert into tbl1 values (1, 12, 12, 23, 1), (2, 23, 33, 1, 0), (3, 333, 2, 21, 1);
然後,我嘗試了查詢
mysql> SELECT * -> FROM tbl1 -> WHERE type=1 AND RAND() <= 0.01 -> UNION -> SELECT * -> FROM tbl1 -> WHERE type =0 ; +----+------+------+------+------+ | id | col1 | col2 | col3 | type | +----+------+------+------+------+ | 2 | 23 | 33 | 1 | 0 | +----+------+------+------+------+ 1 row in set (0.00 sec) mysql>
你的語法很好!
您的錯誤消息有
UNION SELECT * SELECT *
問題。仔細看一下消息:ERROR 1064 (42000): 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 'UNION SELECT * SELECT * FROM tbl1 WHERE type =0 )' at line 3
我看到
SELECT *
兩次和一個尾隨右括號,您沒有在查詢中輸入。如果您的 MySQL 版本始終產生該錯誤消息,那麼您的查詢解析器中肯定存在錯誤。您可能需要升級 MySQL 作為最後的手段。
建議
您
CREATE TABLE
將沒有任何索引。嘗試使用索引創建表並隨後載入
create table tbl2 like tbl1; insert into tbl2 SELECT * FROM tbl1 WHERE type=1 AND RAND() <= 0.01 UNION SELECT * FROM tbl1 WHERE type =0 ;