列名之前的At-Sign in SQL语句 [英] At-Sign in SQL statement before column name

问题描述

我在一个PHP文件中有一个INSERT语句，其中在列名的前面出现了@符号.

I have an INSERT statement in a PHP-file wherein at-signs (@) are occurring in front of the column name.

@ field1， @ field2，

@field1, @field2,

这是一个MySQL数据库.标志处是什么意思?

It is a MySQL database. What does the at-sign mean?

修改:
PHP脚本中没有SET @field1 := 'test'. PHP脚本读取一个csv并将数据放入表中.可以将其用作注释功能吗?

There is no SET @field1 := 'test' in the PHP script. The PHP script reads a csv and puts the data into the table. Can it be misused as a commenting out feature?

<?php
$typo_db_username = 'xyz';  //  Modified or inserted by TYPO3 Install Tool.
$typo_db_password = 'xyz';  //  Modified or inserted by TYPO3 Install Tool.

// login
$_SESSION['host'] = "localhost";
$_SESSION['port'] = "3306";
$_SESSION['user'] = $typo_db_username;
$_SESSION['password'] = $typo_db_password;
$_SESSION['dbname'] = "database";


$cxn = mysqli_connect($_SESSION['host'], $_SESSION['user'], $_SESSION['password'], $_SESSION['dbname'], $_SESSION['port']) or die ("SQL Error:" . mysqli_connect_error() );
mysqli_query($cxn, "SET NAMES utf8");

$sqltrunc = "TRUNCATE TABLE tablename";
$resulttrunc = mysqli_query($cxn,$sqltrunc) or die ("Couldn’t execute query: ".mysqli_error($cxn));

$sql1 = "
LOAD DATA LOCAL
INFILE 'import.csv'
REPLACE
INTO TABLE tablename
FIELDS
TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(
`normalField`,
@field1,
@field2,
`normalField2`,
@field3,
@field4
)";

$result1 = mysqli_query($cxn,$sql1) or die ("Couldn’t execute query: " . mysqli_error($cxn));


?>'

解决方案:

最后，我发现了！ @字段用作虚拟对象，会漏掉csv文件中的一列.参见 http://www.php-resource.de/forum/showthread/t-97082.html http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Finally, I found it out! The @ field is used as dummy to miss out a column in a csv-file. See http://www.php-resource.de/forum/showthread/t-97082.html http://dev.mysql.com/doc/refman/5.0/en/load-data.html

推荐答案

@符号是SQL中的变量.

The @ sign is a variable in SQL.

在MySQL中，它用于在查询的连续运行之间存储一个值，或在两个不同的查询之间传输数据.

In MySQL it is used to store a value between consecutive runs of a query, or to transfer data between two different queries.

一个例子

在两个查询之间传输数据

SELECT @biggest:= MAX(field1) FROM atable;
SELECT * FROM bigger_table WHERE field1 > @biggest;

排名中的另一种用法是MySQL不提供本机支持.

Another usage is in ranking, which MySQL doesn't have native support for.

存储连续查询的值

INSERT INTO table2
  SELECT @rank := @rank + 1, table1.* FROM table1
  JOIN( SELECT @rank := 0 ) AS init
  ORDER BY number_of_users DESC

请注意，要使此功能正常运行，必须固定查询中处理行的顺序，这很容易出错.

Note that in order for this to work, the order in which the rows get processed in the query must be fixed, it's easy to get this wrong.

请参阅:
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
mysql排序和排名声明
http://www.xaprb.com /blog/2006/12/15/advanced-mysql-user-variable-techniques/

See:
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
mysql sorting and ranking statement
http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/

更新
此代码将永远无法使用.
您之前刚刚打开了连接，而@fields却没有设置.
因此，当前它们保存null值.
最重要的是，您不能使用@vars表示字段名称，您可以仅将@vars用作值.

UPDATE
This code will never work.
You've just opened the connection before and nowhere are the @fields set.
So currently they hold null values.
To top that, you cannot use @vars to denote fieldnames, you can only use @vars for values.

$sql1 = "
LOAD DATA LOCAL INFILE 'import.csv'
REPLACE INTO TABLE tablename
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(`normalField`, @field1, @field2, `normalField2`, @field3, @field4)";

这篇关于列名之前的At-Sign in SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！