Virtual columns in Mysql sintax error

#1

I am Trying to create viertual columns in a table:

create table database1.table1 (columnA int not null,
columnB varchar(32),
columnC int as (columnA mod 10) virtual,
columnD varchar(5) as (left(columnB,5)) persistent);

What I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that
corresponds to your MySQL server version for the right syntax to use near
'as (c
olumnA mod 10) virtual,
columnD varchar(5) as (left(columnB,5)) persistent)' at line 3

Y am running mysql server 5.5 on windows

Can you help me?

Thaks in advance

#2

On 10/7/2011 7:29 AM, CENTRINO wrote:

Quote:

I am Trying to create viertual columns in a table:

create table database1.table1 (columnA int not null,
columnB varchar(32),
columnC int as (columnA mod 10) virtual,
columnD varchar(5) as (left(columnB,5)) persistent);

What I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that
corresponds to your MySQL server version for the right syntax to use near
'as (c
olumnA mod 10) virtual,
columnD varchar(5) as (left(columnB,5)) persistent)' at line 3

Y am running mysql server 5.5 on windows

Can you help me?

Thaks in advance

I don't believe MySQL supports virtual columns (yet). Maybe in 6.0
(Axel would have to confirm that).

You should be able to do it with a VIEW, however.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#3

Quote:

I am Trying to create viertual columns in a table:

create table database1.table1 (columnA int not null,
columnB varchar(32),
columnC int as (columnA mod 10) virtual,
columnD varchar(5) as (left(columnB,5)) persistent);

What I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that
corresponds to your MySQL server version for the right syntax to use near
'as (c
olumnA mod 10) virtual,
columnD varchar(5) as (left(columnB,5)) persistent)' at line 3

Y am running mysql server 5.5 on windows

But you tested on MariaDB? MySQL does not support virtual columns, but
MariaDB does.

--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

#4

Thanks Willem

Ok Ok I am new to MySQL

For what I can see, there are several DBengines, among them, MariaDB. But
MySQL 5.5 does not come with MariaDB as you can see.

It is still not ready. Will it come as an update of MySQL ? Or will I Have
to install it separatedly?

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

Quote:

Engine | Support | Comment | Transactions | XA
Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
FEDERATED | NO | Federated MySQL storage engine
NULL | NULL | NULL |
MRG_MYISAM | YES | Collection of identical MyISAM tables
NO | NO | NO |
MyISAM | YES | MyISAM storage engine | NO
NO | NO |
BLACKHOLE | YES | /dev/null storage engine (anything you
write to it disappears) | NO | NO | NO |
CSV | YES | CSV storage engine | NO
NO | NO |
MEMORY | YES | Hash based, stored in memory, useful for
temporary tables | NO | NO | NO |
ARCHIVE | YES | Archive storage engine | NO
NO | NO |
InnoDB | DEFAULT | Supports transactions, row-level locking,
and foreign keys | YES | YES | YES |
PERFORMANCE_SCHEMA | YES | Performance Schema | NO
NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

"Willem Bogaerts" <w.bogaerts (AT) kratz (DOT) nl> escribió en el mensaje
news:4e8f1a46$0$2448$e4fe514c (AT) news2 (DOT) news.xs4all.nl...

Quote:

I am Trying to create viertual columns in a table:

create table database1.table1 (columnA int not null,
columnB varchar(32),
columnC int as (columnA mod 10) virtual,
columnD varchar(5) as (left(columnB,5)) persistent);

What I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual
that
corresponds to your MySQL server version for the right syntax to use near
'as (c
olumnA mod 10) virtual,
columnD varchar(5) as (left(columnB,5)) persistent)' at line 3

Y am running mysql server 5.5 on windows

But you tested on MariaDB? MySQL does not support virtual columns, but
MariaDB does.

--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

#5

On 07-10-2011 18:14, CENTRINO wrote:

Quote:

Ok Ok I am new to MySQL

For what I can see, there are several DBengines, among them, MariaDB. But
MySQL 5.5 does not come with MariaDB as you can see.

It is still not ready. Will it come as an update of MySQL ? Or will I Have
to install it separatedly?

http://mariadb.org/

Its a different database....

--
Luuk

#6

El 07/10/2011 18:14, CENTRINO escribió/wrote:

Quote:

Ok Ok I am new to MySQL

For what I can see, there are several DBengines, among them, MariaDB. But
MySQL 5.5 does not come with MariaDB as you can see.

It is still not ready. Will it come as an update of MySQL ? Or will I Have
to install it separatedly?

MySQL supports different storage engines but MariaDB is not one of them:
it's an entirely different database server forked from MySQL. It looks
like you've installed MySQL but are reading the MariaDB documentation.
You have to pick one.

--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

#7

=?ISO-8859-1?Q?=22=C1lvaro_G=2E_Vicario=22?= <alvaro.NOSPAMTHANX (AT) demogracia (DOT) com.invalid> wrote:

Quote:

MySQL supports different storage engines but MariaDB is not one of them:
it's an entirely different database server forked from MySQL.

Just to make this clear: this work started as the "Maria" storage
engine for MySQL shortly after Oracle bought InnoBase (the makers
of the InnoDB storage engine). When Monty Widenius left Sun, he
started a MySQL fork named "MariaDB" and the Maria storage engine
is now called "Aria".

Besides the Aria storage engine (which should theoretically plug
into any other MySQL fork) there are also some unique features in
the SQL layer of MariaDB. One of those are virtual columns.

IMHO virtual columns are a misconception. They add redundant data
to the database. The VIRTUAL flavor does not even support indexes,
so they can be replaced by the respective formula in SELECT or by
a view. The PERSISTENT flavor is a little more useful (indexed!)
but can easily be replaced by a real column and some triggers.

XL

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Virtual columns in Mysql sintax error

comp.databases.mysql comp.databases.mysql

Virtual columns in Mysql sintax error - 10-07-2011 , 06:29 AM

Re: Virtual columns in Mysql sintax error - 10-07-2011 , 08:50 AM

Re: Virtual columns in Mysql sintax error - 10-07-2011 , 10:27 AM

Re: Virtual columns in Mysql sintax error - 10-07-2011 , 11:14 AM

Re: Virtual columns in Mysql sintax error - 10-07-2011 , 11:19 AM

Re: Virtual columns in Mysql sintax error - 10-10-2011 , 04:16 AM

Re: Virtual columns in Mysql sintax error - 10-10-2011 , 06:47 AM