Hi, I want to import some data exported from mysqldump. Some of the sqls are like the followings.

DROP TABLE IF EXISTS `onl_cgform_button`;
CREATE TABLE `onl_cgform_button`  (
  `ID` varchar(32) CHARACTER NOT NULL,
  `BUTTON_CODE` varchar(50) CHARACTER NULL DEFAULT NULL,
  `BUTTON_ICON` varchar(20) CHARACTER NULL DEFAULT NULL,
  `BUTTON_NAME` varchar(50) CHARACTER NULL DEFAULT NULL,
  `BUTTON_STATUS` varchar(2) CHARACTER NULL DEFAULT NULL,
  `BUTTON_STYLE` varchar(20) CHARACTER NULL DEFAULT NULL,
  `EXP` varchar(255) CHARACTER NULL DEFAULT NULL,
  `CGFORM_HEAD_ID` varchar(32) CHARACTER NULL DEFAULT NULL,
  `OPT_TYPE` varchar(20) CHARACTER NULL DEFAULT NULL,
  `ORDER_NUM` int(11) NULL DEFAULT NULL,
  `OPT_POSITION` varchar(3) CHARACTER NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `idx_ocb_CGFORM_HEAD_ID`(`CGFORM_HEAD_ID`) USING BTREE,
  INDEX `idx_ocb_BUTTON_CODE`(`BUTTON_CODE`) USING BTREE,
  INDEX `idx_ocb_BUTTON_STATUS`(`BUTTON_STATUS`) USING BTREE,
  INDEX `idx_ocb_ORDER_NUM`(`ORDER_NUM`) USING BTREE
);

But sqlite3 does not support this syntax above, I have to convert to the followings.

DROP TABLE IF EXISTS `onl_cgform_button`;
CREATE TABLE `onl_cgform_button`  (
  `ID` varchar(32) NOT NULL,
  `BUTTON_CODE` varchar(50) NULL DEFAULT NULL,
  `BUTTON_ICON` varchar(20) NULL DEFAULT NULL,
  `BUTTON_NAME` varchar(50) NULL DEFAULT NULL,
  `BUTTON_STATUS` varchar(2) NULL DEFAULT NULL,
  `BUTTON_STYLE` varchar(20) NULL DEFAULT NULL,
  `EXP` varchar(255) NULL DEFAULT NULL,
  `CGFORM_HEAD_ID` varchar(32) NULL DEFAULT NULL,
  `OPT_TYPE` varchar(20) NULL DEFAULT NULL,
  `ORDER_NUM` int(11) NULL DEFAULT NULL,
  `OPT_POSITION` varchar(3) NULL DEFAULT NULL,
  PRIMARY KEY (`ID`)
);
CREATE INDEX `idx_ocb_CGFORM_HEAD_ID` on `onl_cgform_button` (`CGFORM_HEAD_ID`);
CREATE INDEX `idx_ocb_BUTTON_CODE` on `onl_cgform_button` (`BUTTON_CODE`);
CREATE INDEX `idx_ocb_BUTTON_STATUS` on `onl_cgform_button` (`BUTTON_STATUS`);
CREATE INDEX `idx_ocb_ORDER_NUM` on `onl_cgform_button` (`ORDER_NUM`);

Is there any suggestions on how to import data from mysql or how can I make sqlite3 support the sql syntax mysql used?

SQLite does not support the INDEX clause as a part of the CREATE statement, so you do it just like you show, using the CREATE INDEX statement.

...so you do it just like you show, using the CREATE INDEX statement.

Same applies for constraints but you need to create them before adding data to the table.

What about indexes? Create the indexes before or after adding data to the table? I guess insertions into the table will be faster without the indexes already added but the index creation might take longer.

Thanks, I got it.

But I still want to ask a question, is there any suggestions on how to import data from mysql/mysqldump?

This question was essentially fully answered in your other thread. The only reply there contains the solution, including some comments about comment handling which you then repeated in your reply to it.

But to paraphrase, there are many "translator" options if you look online which will parse the one dialect of SQL, especially the non-standard comments, into a form that is digestible by sqlite.

Good luck!

The importing of the data is pretty standard - insert into table X. It's the table creation which is different as MySQL supports datatype X which sqlite doesn't, etc. So once you've tweaked the SQL for the table creation to ensure it's sqlite 'legal', the rest of the import should go through nicely.