mysql - 如何将一个表中的值传递给sql中另一个表的新列？

Question

我创建了一个名为 users 的表来存储用户的详细信息。用户表有字段调用 user_id(primary key)、name、email、password、timestamps。之后，我创建了另一个名为“test”的表。测试表列是 id（主键自动增量）、uid（用户 id）、名称、描述（一些随机字符串）。

我想将 user_id 和 name 的值分别传入测试表的 uid 和 name 列。为此，我创建了一个触发器函数。通过使用触发器函数，我想将值传递给“test”表中的 uid 和 name 字段。但我得到了如下错误。

  Traceback (most recent call last):
 File "/home/sameera/anaconda3/envs/customerbot/lib/python3.6/tkinter/__init__.py", line 1705, in __call__
  return self.func(*args)
  File "gui_bot.py", line 152, in send
   cursor.execute(insertion)
   File "/home/sameera/.local/lib/python3.6/site-packages/pymysql/cursors.py", line 163, in execute
  result = self._query(query)
  File "/home/sameera/.local/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query
  conn.query(q)
  File "/home/sameera/.local/lib/python3.6/site-packages/pymysql/connections.py", line 505, in query
  self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/sameera/.local/lib/python3.6/site-packages/pymysql/connections.py", line 724, in _read_query_result
  result.read()
  File "/home/sameera/.local/lib/python3.6/site-packages/pymysql/connections.py", line 1069, in read
  first_packet = self.connection._read_packet()
   File "/home/sameera/.local/lib/python3.6/site-packages/pymysql/connections.py", line 676, in _read_packet
   packet.raise_for_error()
   File "/home/sameera/.local/lib/python3.6/site-packages/pymysql/protocol.py", line 223, in raise_for_error
    err.raise_mysql_exception(self._data)
    File "/home/sameera/.local/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
    pymysql.err.OperationalError: (1054, "Unknown column 'test.uid' in 'where clause'")

用户表结构

     CREATE TABLE users(
        user_id int(20) PRIMARY KEY AUTO_INCREMENT,
        name varchar(255),
        email varchar(255),
        password varchar(255),
    )

测试表结构

   CREATE TABLE test(
        id int(20) PRIMARY KEY AUTO_INCREMENT,
        uid int(20),
        name varchar(255),
        description varchar(255)
    )

触发功能

"CREATE TRIGGER after_user_insert 
 AFTER INSERT 
        ON users FOR EACH ROW 
  BEGIN 
  INSERT INTO test(uid,name) 
      VALUES(NEW.user_id, NEW.name); 
   END;"

插入功能

"""INSERT INTO test(uid,name)
    Select name,user_id 
    From users
    Where users.user_id = test.uid
 """

任何人都可以解释实施的问题。提前致谢。

标签： mysql