CODEWITHSUNDEEP
mysqlsql
BrushyAmoeba
BrushyAmoeba

Reputation: 198

MySQL Table Comment Spanning Multiple Lines

When creating a table in MySQL we can specify a comment.

Is anyone aware of a way to have the string passed to COMMENT = span multiple lines?

I've tried using a local variable that is the result of a CONCAT and also have tried using string literal syntax like 'foo ' 'bar ' 'baz'

For example:

CREATE TABLE foo (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) COMMENT = 'some really long string that we want to span multiple lines instead of being limited to a single line';

Upvotes: 6

Views: 2195

Answers (3)

Hossein J
Hossein J

Reputation: 11

Simple lines can be like:

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="user",
    password="pwd",
    database="dbname"
)

mycursor = mydb.cursor()
table_string_construct = """
    CREATE TABLE foo (
        company VARCHAR (255),
        col2 VARCHAR (255),
        col3 VARCHAR (255)
        )
    """
mycursor.execute(table_string_construct)

Upvotes: 1

Zack
Zack

Reputation: 2341

To go along with @BillKarwin's answer: If you want to use multiple lines to create the comment, just do so:

CREATE TABLE foo
(
    foo_id int primary key auto_increment,
    foo_name varchar(255)
)
COMMENT = 'this is a 
multi-line comment'

Unfortunately, you can't do any kind of concatenation in your comment construction (to split the actual DDL across multiple lines, while not having a line break in the actual DDL). For example, this is not possible:

CREATE TABLE foo(foo_id INT PRIMARY KEY AUTO_INCREMENT) 
COMMENT = CONCAT('multi-line', 
    ' comment'

Of course, if you're accessing MySQL through a client language (PHP or Python or something), you can construct the string however you want:

ddl = (     "CREATE TABLE `foo` "
            "(`foo_id` INT PRIMARY KEY AUTO_INCREMENT) "
            "COMMENT='This is a multi-line "
            "coment")

Upvotes: 5

Bill Karwin
Bill Karwin

Reputation: 562260

Use \n if you want to embed a newline.

CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='some really long string\nthat we want to span multiple lines\ninstead of being limited to a single line'

The comment will still appear as one long string if you use SHOW CREATE TABLE to view it. That statement returns the exact syntax it would take to recreate the table, so if it requires using \n to put newlines in, that's what it will show.

But you can output the newlines as actual line breaks if you query the table metadata from the INFORMATION_SCHEMA:

mysql> select * from information_schema.tables where table_name='foo'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: foo
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2018-10-02 23:37:57
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: some really long string
that we want to span multiple lines
instead of being limited to a single line

Upvotes: 1

Related Questions