At-Sign in SQL statement before column name

testing picture testing · Aug 29, 2011 · Viewed 9.8k times · Source

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

@field1, @field2,

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

Edit:
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));


?>'

SOLUTION:

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

Answer

Johan picture Johan · Aug 29, 2011

The @ sign is a variable in SQL.

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

An example

Transfer data between two queries

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

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

Store a value for consecutive runs of a query

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.

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/

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)";