Ошибка 1327 sql

select into from and insert into select are used to copy tables

The main differences between the two are:

1) select into from requires that the target table does not exist, because it will be created automatically when inserting.

2) insert into select from requires the existence of the target table

Two, SELECT INTO FROM statement
The statement form is:
SELECT vale1, value2 into Table2 from Table1
requires that the target table Table2 does not exist, because the table Table2 will be automatically created when inserting, and the specified field data in Table1 will be copied to Table2.

Summarize the basic statement of the copy table (copy from the old table to the new table):

1. create table newTable as select * from oldTabel; not only copy the table structure, but also copy the table content;

2. create table newTable as select * from oldTable where 1=2; Only copy the table structure, not the table content;

3. Insert into newTable select * form oldTable; does not copy the table structure, only the table content

Or select value1, value2 into newTable form oldTable;

ERROR 1327 (42000): Undeclared variable:

select into from prompts Undeclared variable….. wrong solution ＆＆ select into from and insert into select usage and difference

However in use todayWhen SELECT INTO FROM is backing up the mysql data table, it keeps returning [Err] 1327-Undeclared variable: …… this kind of error when running the relevant sql statement. I found out after querying relevant information. The database does not support the SELECT INTO FROM statement, but after research, another workaround can be used to solve this problem. Let’s talk about how to solve this error.！

mysql> select user,host into user2 from user;
ERROR 1327 (42000): Undeclared variable: user2

The solution is:

create table user2 (select * from user);This method will copy all the content in the old_table. In this method, you need to pay attention to the fact that the primary key, Extra, auto_increment and other attributes in the old_table are not included in the new_table, and you need to add it manually. For details, see the modified table that is the field. Attributes.

mysql> create table user2 (select * from user);
Query OK, 6 rows affected (0.18 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> create table user01 (select user,password,host from user);
Query OK, 6 rows affected (0.20 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from user01;
+------+----------+-----------+
| user | password | host      |
+------+----------+-----------+
| root |          | localhost |
| root |          | lmr       |
| root |          | 127.0.0.1 |
| root |          | ::1       |
|      |          | localhost |
|      |          | lmr       |
+------+----------+-----------+
6 rows in set (0.00 sec)

mysql> desc user01;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| user     | char(16) | NO   |     |         |       |
| password | char(41) | NO   |     |         |       |
| host     | char(60) | NO   |     |         |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Note: When SELECT INTO copies a table or table structure, it only gets a "shell", just like a cloned human being, but only a body is obtained, and personal consciousness and memories will not be cloned. The primary keys, foreign keys, constraints, triggers, and indexes of the original table will not be copied

CREATE TABLE table name AS SELECT statement

1. The new table does not exist
create table new_table select * from old_talbe;

2. The new table exists
Copy the old table data to the new table (assuming the two tables have the same structure)
insert into new_table select * from old_table;

Copy the old table data to the new table (assuming that the two tables have different structures)
insert into new_table(field1,field2,…..) select field1,field2,field3 from old_table;

Copy all data
select * into new_table from old_table;

Only copy the table structure to the new table
select * into new_talble from old_table where 1=2;

EG:

mysql> create table  marketing_automation_preview_logs_new like  marketing_automation_preview_logs;

mysql> insert into marketing_automation_preview_logs_new (select  *   from marketing_automation_preview_logs  where act_time> ‘1588521600’);

mysql> select count(*) from marketing_automation_preview_logs_new;
 

mysql> select  count(*)   from marketing_automation_preview_logs  where act_time> ‘1588521600’;

mysql> select count(*) from marketing_automation_preview_logs;

to sum up:

create table  marketing_automation_preview_logs_new like  marketing_automation_preview_logs;

insert into marketing_automation_preview_logs_new (select  *   from marketing_automation_preview_logs  where act_time> ‘1588521600’);

RENAME TABLE marketing_automation_preview_logs TO marketing_automation_preview_logs20200512_back;

 RENAME TABLE marketing_automation_preview_logs_new TO marketing_automation_preview_logs;

show index from  marketing_automation_preview_logs;

select count(*) from marketing_automation_preview_logs;

mysql8 Reference Manual—SELECT … INTO Statement

SELECT … INTO form SELECT stores the query result in a variable or writes it to a file:
SELECT … INTO var_list Select column values ​​and store them in variables.
SELECT … INTO OUTFILE write the selected line to the file. You can specify column and row terminators to generate specific output formats.
SELECT … INTO DUMPFILE Write a single line to the file without any format.

https://dev.mysql.com/doc/refman/8.0/en/select-into.html

I got an error code: 1327.

Undeclared variable; order_id.

I been looking at the code for hours, can not figure out where the error would. I thought it might link to Select last insert()..

USE Data_Name

DROP PROCEDURE IF EXISTS test;

DELIMITER //

PROCEDURE test()
BEGIN
    DECLARE sql_error INT DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SET sql_error = TRUE;

START TRANSACTION;

INSERT INTO orders VALUES
    (DEFAULT, 3, NOW(), '10.00', '0.00', NULL, 4,
    'American Express', '378282246310005', '04/2013', 4);

SELECT LAST_INSERT_ID()INTO order_id;

INSERT INTO order_items VALUES
    (DEFAULT, order_id, 6, '415.00', '161.85', 1);

INSERT INTO order_items VALUES
    (DEFAULT, order_id, 1, '699.00', '209.70', 1);

IF sql_error = FALSE THEN
    COMMIT;
SELECT 'The transaction was committed.';
ELSE
    ROLLBACK;
SELECT 'The transaction was rolled back.';
END IF;
END//