javamariadbmariadb-connect-engine

On the insertion dynamic column of mariaDB


set @v1 = "'city', 'ITY', 'age', '20'";
select @v1;
insert into test values (11, 'hidy', column_create(@v1));

MariaDB does not support this variable in the form of an insert,and

client Error at))
MariaDB :10.1.19
client :heidi 9.4.0.5130 
OS: Windows 10

How can I insert in variable form? Because I want to use JDBC in my JAVA to pass parameters


Solution

  • You can use prepared statements for INSERT.

    MariaDB [test]> set @v1 = "'city', 'ITY', 'age', '20'";
    Query OK, 0 rows affected (0.01 sec)
    
    MariaDB [test]> set @q = concat("insert into test values(11, 'hidy', column_create(",@v1,"))");
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [test]> select @q;
    +--------------------------------------------------------------------------------+
    | @q                                                                             |
    +--------------------------------------------------------------------------------+
    | insert into test values(11, 'hidy', column_create('city', 'ITY', 'age', '20')) |
    +--------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    MariaDB [test]> prepare stmt from @q;
    Query OK, 0 rows affected (0.00 sec)
    Statement prepared
    
    MariaDB [test]> execute stmt;
    Query OK, 1 row affected (0.26 sec)
    
    MariaDB [test]> select column_get(b,'city' as char(8)) from test;
    +---------------------------------+
    | column_get(b,'city' as char(8)) |
    +---------------------------------+
    | ITY                             |
    +---------------------------------+
    1 row in set (0.00 sec)