I can not create or modify a JSON type column in MySQL

Question

I can not create or modify a JSON type column in MySQL

Navigation

#1 by (1 votes)

3

I'm trying to add a column to store JSON objects in my MySQL database.

My configuration is as follows:

Versión de MySQL:    5.6.32-78.1.
Engine de la tabla:  MyIsam.
Base de datos alojada en un hosting compartido (Bluehost)

I've tried it this way:

ALTER TABLE 'liturgia_horas_completas_salmos' 
ADD COLUMN 'json_data' JSON AFTER  'salmos';

And throw me the error:

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 'JSON AFTER salmos ' at line 2

I have also created a column of type TEXT :

ALTER TABLE 'liturgia_horas_completas_salmos' 
ADD COLUMN 'json_data' TEXT AFTER  'salmos';

It is created well, then I enter a valid json in it and try to change it to JSON like this:

ALTER TABLE 'liturgia_horas_completas_salmos' MODIFY 'json_data' JSON;

And I also get an error:

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 'JSON' at line 1

What could I do to have a column with JSON data type?

mysql json

asked by A. Cedano 22.05.2018 в 15:34

source

1 answer

Retrieve related field in another table MYSQL Difference between FLOAT and DOUBLE

score 1 · Accepted Answer

The JSON data type in the MySQL database manager was included as of version 5.7 and in MariaDB since version 10.2.7, also the link

Reference: JSON DATA TYPE

Reference for MariDB: JSON DATA TYPE

Annex to the above I leave you an example of the syntax to be handled

WE CREATE THE DATABASE WHERE WE WILL WORK

CREATE DATABASE ols;

WE MAKE USE OF THE NEWLY CREATED DATABASE

USE ols;

WE CREATE A TABLE

CREATE TABLE demo(
 name VARCHAR(100),
 edad INT NOT NULL
)ENGINE=myISAM;

WE MAKE AN ALTER TO ADD A JSON TYPE COLUMN

alter table demo 
add column propiedades JSON not null
after edad;