Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.
Solved
Issue with MySQL VIEW column size
Posted on 2009-04-13
Hi,
I have Product table with columns prod_id int(11),category_id int(11),prod_name varchar(255) and prod_quantity varchar(255).
I am creating VIEW in MySQL using above table and following syntax:
CREATE OR REPLACE VIEW product_view AS
SELECT
category_id,
CONCAT("#",GROUP_CONCAT(CO
FROM
product
GROUP BY category_id
Now when i describe VIEW then the column size of VIEW COLUMN "product_quantity_values" shows varchar(343). So this view only allow me to store 343 characters in COLUMN "product_quantity_values" . More than that are truncated.
Is there any way to avoid such behavior? I want the VIEW COLUMN "product_quantity_values" should store more characters.
I have Product table with columns prod_id int(11),category_id int(11),prod_name varchar(255) and prod_quantity varchar(255).
I am creating VIEW in MySQL using above table and following syntax:
CREATE OR REPLACE VIEW product_view AS
SELECT
category_id,
CONCAT("#",GROUP_CONCAT(CO
FROM
product
GROUP BY category_id
Now when i describe VIEW then the column size of VIEW COLUMN "product_quantity_values" shows varchar(343). So this view only allow me to store 343 characters in COLUMN "product_quantity_values" . More than that are truncated.
Is there any way to avoid such behavior? I want the VIEW COLUMN "product_quantity_values" should store more characters.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
3
6 Comments
please try this:
CREATE OR REPLACE VIEW product_view AS
SELECT
category_id,
cast(CONCAT("#",GROUP_CONCAT(CONCAT_WS('=',prod_name,prod_quantity) SEPARATOR '#'),"#") AS product_quantity_values as VARCHAR(65000))
FROM
product
GROUP BY category_id
Hi angelIII,
Its giving a error:
Error Code : 1064
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 fproduct_quantity_values as VARCHAR(65000))
BTW: I am using MySQL 5
Its giving a error:
Error Code : 1064
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 fproduct_quantity_values as VARCHAR(65000))
BTW: I am using MySQL 5
sorry,...
CREATE OR REPLACE VIEW product_view AS
SELECT
category_id,
cast(CONCAT("#",GROUP_CONCAT(CONCAT_WS('=',prod_name,prod_quantity) SEPARATOR '#'),"#") as VARCHAR(65000)) AS product_quantity_values
FROM product
GROUP BY category_id
Sorry angelIII,
Same error again.
Error Code : 1064
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 'VARCHAR(65000)) AS
Same error again.
Error Code : 1064
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 'VARCHAR(65000)) AS
Hi,
Above issue has been resolved.
MySQL 5 CAST function allows type as CHAR Not VARCHAR.
But thanks for giving way to tackle situation.
Above issue has been resolved.
MySQL 5 CAST function allows type as CHAR Not VARCHAR.
But thanks for giving way to tackle situation.
Featured Post
Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
- Software-Other
- Databases
- MySQL Server
- MongoDB
- NoSQL Databases
- Percona, *Monitoring tool
This video shows how to Export data from an Oracle database using the Original Export Utility. The corresponding Import utility, which works the same way is referenced, but not demonstrated.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month9 days, 10 hours left to enroll
722 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.