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

问题描述

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

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 数据库.at 符号是什么意思?

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.htmlhttp://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/

更新
此代码将永远无法工作.
您之前刚刚打开了连接，但没有设置@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)";

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