Insert into table concatenation of variables and constant tokens

Question

I have a table with the following schema

CREATE TABLE `foo` (
  `myfield` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I've defined a couple of variables as follows

SET @NAME := 'Somename';
SET @AGE := '31';

I need to insert into the table an entry composed of the concatenation of these user-defined variables and constant tokens. I've tried the following query

INSERT INTO `foo` (`myfield`) VALUES
('Name: ' + @NAME + ' Age: ' + @AGE);

The statement execution succeds, but after insertion myfield equals to:

It's just like the first part of the inserted value is ignored. What am I missing?

+ is not a mysql string concatenation function use concat verb. — P.Salmon, Nov 12 '17 at 11:48
This is how **NOT** to store data in a database... please don't do it... — Blag, Nov 12 '17 at 12:32
Possible duplicate of [String concatenation in MySQL](https://stackoverflow.com/questions/5975958/string-concatenation-in-mysql) — mickmackusa, Mar 12 '19 at 09:40

score 1 · Answer 1 · edited Nov 12 '17 at 12:31

1

First. Go read about database normalization. The way you hope to store name and age is a terrible misuse of SQL. If you pass your application, written this way, on to other programmers they will curse your name to the thousandth generation. Seriously.

Second. The + operator in MySQL does addition. If you do 'any string' + 42, two things happen:

MySQL sees that it's adding numbers and coerces 'any string' to a number: then number zero.
it adds zero to 42.

You want to use CONCAT('Name: ', @NAME, ' Age: ', @AGE)

edited Nov 12 '17 at 12:31

Blag

5,818
2
22
45

answered Nov 12 '17 at 11:59

O. Jones

103,626
17
118
172

I've taken the liberty to edit you answer, and add another one that explain the full "good way". – Blag Nov 12 '17 at 12:52

score 1 · Answer 2 · answered Nov 12 '17 at 12:51

This how you should deal with this kind of data :

you don't want you age to be the same next year, so you store the date of birth
when you insert, you store one information per field
on the output SELECT you CONCAT what you want .

So :

CREATE TABLE `foo` (
    `NAME` text NOT NULL,
    `BIRTH` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `foo` (`NAME`, `BIRTH`)
VALUES (@NAME, @BIRTH);


SELECT CONCAT(
    'Name: ',
    `foo`.`NAME`,
    ' Age: ',
     TIMESTAMPDIFF(YEAR,`foo`.`BIRTH`,CURDATE())
     )
FROM `foo`

Hi Blag, thanks for the response. Age was just an example. The real case is similar to what you described in this answer, with just only constant (invariable) values, like name or birth. The goal was just to parametrize the query. Thanks for the explanation. — Giorgio, Nov 12 '17 at 13:34

Insert into table concatenation of variables and constant tokens

2 Answers2